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.
Hello Experts,
I am facing issue while creating measure for sum of Sales for max dateid with mapid=5.
Below is the sample data:
Table Name: Dummy
I tried using : Calculate(sum(Sales),Filter(Filter(Dummy,Dummy[mapid]=5),Dummy[dateid]=max(Dummy[dateid])).
Any help would be appreciated.
Thanks
Solved! Go to Solution.
Hi @mwadhwani,
First I want to filter f_WS[MapID]=12.
Subset obtained by above filter : On this I want to apply filter on Max(DateID).
Note: Maximum dateid for Mapid 12 is 634.
My requirement is: Max DateID for MapID=12 which is 634.
Sum is: 1210 but its showing blank value
The formula below should work in this scenario.
Current Ratio = VAR maxProcessDateID = CALCULATE ( MAX ( f_WS[ProcessDateID] ), f_WS[MapID] = 12 ) RETURN CALCULATE ( SUM ( f_WS[Value] ), FILTER ( f_WS, f_WS[MapID] = 12 && f_WS[ProcessDateID] = maxProcessDateID ) )
Regards
Hi @mwadhwani
You may try ...
CALCULATE(
Sum(Sales),
FILTER(
Dummy,
Dummy[mapid]=5 &&
Dummy[dateid]=max(Dummy[dateid])
)
)
Hope That Helps,
Vicente
Thanks for the reply
I tried:
CALCULATE(
Sum(Sales),
FILTER(
Dummy,
Dummy[mapid]=5 &&
Dummy[dateid]=max(Dummy[dateid])
)
)
But I am getting Blank value.
Hi @mwadhwani
Sorry, I made a mistake. You should try ...
CALCULATE(
Sum(Dummy[Sales]),
FILTER(
Dummy,
Dummy[mapid]=5 &&
Dummy[dateid]=max(Dummy[dateid])
)
)
I've tried it on PowerPivot and it works for me ...
Hope That Helps
Vicente
In PowerPivot it works fine. But when we create measure using the DAX it gives blank value.
I am trying to create New Measure using your query:
CALCULATE(
Sum(Dummy[Sales]),
FILTER(
Dummy,
Dummy[mapid]=5 &&
Dummy[dateid]=max(Dummy[dateid])
)
)
But when I plot on say Card visualization it gives blank value.
Hi @mwadhwani
It works for me ...
Hence that, in Spain, we use ";" instead of "," in the formula editor
Using suggested query by @vcastello,I am still getting Blank value again. Check this below pbix:
https://www.dropbox.com/s/qbielpmhexsop2j/CheckThis.pbix?dl=0
My Result screenshot:
IF I hardcode the value(634 which is maximum for mapid 12) instead of using MAX(ProcessDateID) in Filter, I get right output.
But using formula it shows Blank
Your help would be highly appreciated.
Thanks
Hi @mwadhwani
The reason why the formula returns blank() is :
1.- Filters the table f_WS.
2.- Looking for records which f_WS[MapID] = 12
3.- AND
4.- Looking for records which f_WS[ProcessDateID] equals the maximum value of f_WS[ProcessDateID] which is 640.
As there are no records that match that criteria (f_WS[MapID] = 12 AND f_WS[ProcessDateID] = 640) it returns blank()
What are you trying to achieve?
The SUM of f_WS[Value] for the MAXIMUM value of the f_WS[ProcessDateID] for each f_WS[MapID] ?
Vicente
First I want to filter f_WS[MapID]=12.
Subset obtained by above filter : On this I want to apply filter on Max(DateID).
Note: Maximum dateid for Mapid 12 is 634.
My requirement is: Max DateID for MapID=12 which is 634.
Sum is: 1210 but its showing blank value
Thanks a lot @vcastello for replying.
Hi @mwadhwani,
First I want to filter f_WS[MapID]=12.
Subset obtained by above filter : On this I want to apply filter on Max(DateID).
Note: Maximum dateid for Mapid 12 is 634.
My requirement is: Max DateID for MapID=12 which is 634.
Sum is: 1210 but its showing blank value
The formula below should work in this scenario.
Current Ratio = VAR maxProcessDateID = CALCULATE ( MAX ( f_WS[ProcessDateID] ), f_WS[MapID] = 12 ) RETURN CALCULATE ( SUM ( f_WS[Value] ), FILTER ( f_WS, f_WS[MapID] = 12 && f_WS[ProcessDateID] = maxProcessDateID ) )
Regards
Hi @mwadhwani
I've uploaded the modified .pbix file you uploaded before. Check it.
https://www.dropbox.com/s/kb8v28zfyfxlk8w/CheckThismodified.pbix?dl=0
The formula works because there is a filter context introduced by the slicer. It calculates the Maximum DateID for the [MapID] value indicated in the slicer and then sums the values for that DateID and [MapID]
Hope That Helps
Vicente
Thanks @vcastello it indeed helped me. But I want to filter MapID=12 in DAX instead of using slicer for it.
But it can be used as work around.Thank You
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 |
---|---|
107 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |