Dax measure not considering the filter context and not aggregating

Question

Hi,&nbsp;I have the below visual and highlighted Dax is having problem:Below is Dax:noofdays = COUNTROWS(Calendar_)For the below selected filters, noofdays dax should display 60 for month of September 2024 and 62 for month of October 2024. The total aggregate value should be 122 days. But this dax is giving wrong values now.FYI, when I bring in DBName-Points_Id into the visual,it is giving correct values row by row but when removed it doesn't: FYR,My expected output should be like below:PFA file here Portfolio Performance - v2.15 (1).pbixPlease let me know if you need further info!&nbsp;Thanks in advance!SergeiBaklan&nbsp;

sergeibaklan · Answer

In very first visual you have months and some measures. One of measures correctly shows number of days in each month.
If you'd like to calculate number of days in the month (still will be 30 or 31) and aggregate them for each item in filter context, it could be like
noofdays = COUNTROWS(Calendar_) * COUNTROWS('Points')
If I understood your logic correctly.

sergeibaklan · Answer

You need to keep filter on Points
SUMX(
    VALUES( Calendar_[Month-Year] ),
    SUMX(
        VALUES( Points[DBName-Point_Id] ),
        VAR  _target = CALCULATE(
            AVERAGE( TargetTimeSeriesMonth[Dail Units] ),
            TargetTimeSeriesMonth[TargetType] = 1
        )
        VAR  _monthtarget = CALCULATE(
            SUM( TargetTimeSeriesMonth[Usage] ),
            TargetTimeSeriesMonth[TargetType] = 1
        )
        VAR  _noofdays = CALCULATE(
                COUNTROWS( Calendar_ ) * COUNTROWS( Points ),
                KEEPFILTERS( Points )
            )
        VAR  _nofinvdays = CALCULATE(
            COUNTROWS( DataInvoice ),
            KEEPFILTERS( Points )
            )
        VAR  _missing = _noofdays - _nofinvdays
        VAR  Result =
        IF(
            _missing = _noofdays,
            _monthtarget,
            _target * _missing
        )
        RETURN Result
    )
)

&nbsp;

sergeibaklan · Answer

I guess you have to exclude credits, i.e.
nofinvdays=
CALCULATE(
    COUNTROWS( DataInvoice ),
    DataInvoice[Invoice / Credit] = "1"
)
    - CALCULATE(
        COUNTROWS( DataInvoice ),
        DataInvoice[Invoice / Credit] = "-1"
    )
But that's only guess, I have no idea how this business works.

excellove15 · Answer

Hi Sir,&nbsp;Thanks for your quick response!🙂This dax works like a gem and I have implemented this in another dax called 'Forecast Budget Unit (TimeSeries)'Dax :Forecast Budget Unit (TimeSeries) = 
SUMX(
    VALUES(Calendar_[Month-Year]),
    SUMX(
        VALUES(Points[DBName-Point_Id]),
        VAR _target = CALCULATE(
            AVERAGE(TargetTimeSeriesMonth[Dail Units]),
            TargetTimeSeriesMonth[TargetType] = 1
        )
        VAR _monthtarget = CALCULATE(
            SUM(TargetTimeSeriesMonth[Usage]),
            TargetTimeSeriesMonth[TargetType] = 1
        )
        VAR _noofdays = COUNTROWS(Calendar_) * COUNTROWS(Points)
        VAR _nofinvdays = COUNTROWS(DataInvoice)
        VAR _missing = _noofdays - _nofinvdays
        RETURN
        IF(
            _missing = _noofdays,
            _monthtarget,
            _target * _missing
        )
    )
)When I used it in visual, the aggregate shows different value 6,139.11 instead of aggregating to 1,665.07.PFA file here Portfolio Performance - v2.15 (1).pbixPlease advise!&nbsp;Thanks in advance!SergeiBaklan&nbsp;

excellove15 · Answer

Hi Sir,&nbsp;Thanks for your quick response and a wonderful solution! 🙂The totals match perfectly!&nbsp;When I tested your Dax measure, I found the below in the visual:I took the variables from Forecast Budget Unit (Timeseries) Dax and created 3 new separate measures to test it.Found that missing Dax is in negative (-62) and noofdays Dax contains 31 days. This makes me doubt that I made some mistake in Dax of noofdays. I am struggling to figure it out.My requirement for:noofdays Dax - how many days are there in the given month-year for each point i.e at a point levelnofinvdays Dax- how many invoice days are there for the given month year and pointmissing Dax - therefore how many days do i have missingFor example, April 2024 has 30 days, and a particular point might have 20 days in the invoice. so 30-20=10 missing days for that point in that monththerefore 10*average forecast rate (_target variable in above measure) = your forecast budget Unit (Timeseries).Above 3 days Dax form integral part of Forecast Budget Unit (Timeseries). Could you please help me with logic?PFA file here Portfolio Performance - v2.15 (1).pbix&nbsp;Thanks in advance!SergeiBaklan&nbsp;

Forum Discussion

Dax measure not considering the filter context and not aggregating

Share

Resources