Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
I added a .PBIX file so it easier to look at. Maybe someone has suggestions how to work around this issue.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
96 | |
75 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |