DAX: How to ignore Row Context in simple measure (PBIX included)

[Titatovenaar2]

Hey everyone,

I have a simple table which has the Year-Quarter column in it, and in the second column I have a measure called "Quantity". I want another measure that returns a 1 for only the LATEST Year-Quarter that holds data for the measure [Quantity]

EDIT: I created a sample dataset that shows a similar problem, you can download it here: PBIX (in here the measure is called "Max_Date_Msr" and I show similar problem on [%DateKey] level instead of Quarters. You will see that Max_Date_Msr shows in table exact same value as the DateKey columns, instead of showing on every record the MAX DateKey, despite the ALL() function)

Year-Quarter	Quantity	New_Measure
2023-01	500	0
2023-02	350	0
2023-03	400	1
2023-04		0

I first try to simply get the Year-Quarter for which data for [Quantity] exists. I try it the following way:

New_Measure =
VAR MaxDate = 
    CALCULATE(
        MAX('DIM Kalender'[%DateKey]),
        FILTER('DIM Kalender', [Quantity] <> 0),
        ALL('DIM Kalender')
    )
VAR LatestQuarterWithData = 
    CALCULATE(
        MAX('DIM Kalender'[Year-Quarter]),
        FILTER(ALL('DIM Kalender'), 'DIM Kalender'[%DateKey] = MaxDate),
        ALL('DIM Kalender')
    )
RETURN
    LatestQuarterWithData

But this results in the following:

Year-Quarter	Quantity	New_Measure
2023-01	500	2023-01
2023-02	350	2023-02
2023-03	400	2023-03
2023-04

So I notices it doesn't actually ignore the row context, but it uses the Year-Quarter as input.

I expected the following, due to the ALL(Calendar) statements in my DAX:

Year-Quarter	Quantity	New_Measure
2023-01	500	2023-03
2023-02	350	2023-03
2023-03	400	2023-03
2023-04		2023-03

The Quantity is a simple measure that summarizes a column of the main Fact table which is connected to DIM Kalender via the %DateKey column.

Can someone help me achieve this rather simple thing and explain me why the ALL(Calendar) actually doesnt ignore the row context in my case?

EDIT: Added PBIX

Kind regards,

Igor

[v-jianboli-msft · Accepted solution]

Hi @Titatovenaar2 ,

Please try:

Measure = 
var _a = SUMMARIZE(ALL('DIM Calendar'),'DIM Calendar'[%DateKey],"Value",SUM('FACT'[Amount]))
var _b = MAXX(FILTER(_a,[Value]>0),[%DateKey])
return IF(SELECTEDVALUE('DIM Calendar'[%DateKey])=_b,1)

Final output:

Best Regards,

Jianbo Li

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

[Titatovenaar2]

I added a .PBIX file so it easier to look at. Maybe someone has suggestions how to work around this issue.