topic Re: measure returns incorrect value when main fact table date is set to MAX(Calendar[Date])-1 in DAX Commands and Tips

measure returns incorrect value when main fact table date is set to MAX(Calendar[Date])-1

Nikolas_ZN — Wed, 01 May 2024 14:40:00 GMT

I have the following code

Change in Course Per Day =

VAR PreviousDate= MAX(Calendar_table[Date])-1

VAR CurrentCourse =

CALCULATE(

MAX(daily_status[heading]),

FILTER(

daily_status,

daily_status[id] = RELATED(Table_ids[id]) &&

Tanker_daily_status[Date] = RELATED(Calendar_table[Date])

)

VAR Previous_course =

CALCULATE(

MAX(daily_status[heading]),

FILTER(

ALLSELECTED(daily_status),

daily_status[id] = RELATED(Table_ids[id]) &&

daily_status[Date] = PreviousDate

)

RETURN

ABS(Current_Course-Previous_course)

When I select from the id_slicer only one id , the final result is correct. That is it calculates correctly the difference between todays and yesterdays headings. When I remove the slicer, then it selects the maximum value from the whole table irrespectively of ids, which is normal.

But I want to find the MAX heading per ID and DATE, so I remove

the ALLSELECTED from the calculations and then for the previous course variable I receive blank which is incorrect.

So what I request is to calculate the heading per id and date for the current date and then calculate heading per id and one day before and calculate their difference.

Relations are

-Calendar Table (one) to (many) daily status

-Table_ID (one) to many daily status

Re: measure returns incorrect value when main fact table date is set to MAX(Calendar[Date])-1

some_bih — Wed, 01 May 2024 20:43:09 GMT

Hi Nikolas_ZN

ALLSELECTED function in DAX is one of the most complex and the best practice is to use it only in visual. If you have measure with ALLSELECTED in definition, and then use this measure as input for another measure the result are impossible to intepreted or to spot issue.

If you only need calculation of difference for previous day, than your simple measure for any day is working right? If yes, then possible options are (maybe there are others):

-move calculation to Calendar table or

-use visual calculation - check link for PREVIOUS

I hope this help

Re: measure returns incorrect value when main fact table date is set to MAX(Calendar[Date])-1

Anonymous — Mon, 06 May 2024 06:26:30 GMT

Hi Nikolas_ZN

Thanks for the reply from some_bih , please allow me to provide another insight:

When you delete the ALLSELECTED() current row can not get values from other rows, so the filter code daily_status[Date] = PreviousDate can not work very well.

So what you need to do is to remove daily_status[Date] = PreviousDate and change the filter code.

For example,

Here I create a set of sample:

Then add a measure:

MEASURE = VAR _currentID = MAX ( 'Table'[ID] ) VAR _currentDate = MAX ( 'Table'[Date] ) RETURN CALCULATE ( MAX ( 'Table'[Heading] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] = _currentDate && 'Table'[ID] = _currentID ) )

the result is as follow:

Here's a blog about contextual filtering for your reference, which you can start with the highlights:

Understand the Filter Context and How to Control i... - Microsoft Fabric Community

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.