Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have data that includes prices at specific dates for different subcategories (customer/location/product/product subcategory)
I want to always show the most recent price in the subtotal, no matter what level I've expanded the matrix to. But my only options are sum/avg/max/min/etc.
The subtotals always show the max or sum or average and not the actual most recent price. In the example below, I want to show 0.1800 as the most recent price, not .2200 because it is the max.
Is that possible with the matrix visual? Some way to tie it to the date field next to it?
Solved! Go to Solution.
Hi @tylerwws,
Could you not do something like this
VAR MaxDate = MAX( Table[Your Lower Level Date Column] )
RETURN
CALCULATE(
MAX( Table[Price] ),
Table[Your Lower Level Date Column] = MaxDate
)
The variable finds the maximum date in the context and then we take the price on that date. At the lowest date level the maximum date will always be the date itself and in the subtotal the variable.
Let me know how you get on.
Kris
Hi @tylerwws,
You need to create a new measure not a new column to do this. There's large restrictions on the functions you can use in a calculted column when using Direct Query but using CALCULATE in a measure is definitely allowed.
Let me know how you get on!
Kris
I created some sample date.
Please try below measure
measure =
VAR maxdate=CALCULATE(MAX('Table (2)'[date]),ALL('Table (2)'))
return CALCULATE(MAX('Table (2)'[price]),FILTER(ALLEXCEPT('Table (2)','Table (2)'[product]),'Table (2)'[date]=maxdate))
Hope this is helpful.
Proud to be a Super User!
But I do want it to show the date specific info at the lowest drill down level. So in your example, the rows for 'a' and 'b' should say 3 and 6, but the other rows should still list the specific prices for that date. Does that make sense?
Hi @tylerwws,
Could you not do something like this
VAR MaxDate = MAX( Table[Your Lower Level Date Column] )
RETURN
CALCULATE(
MAX( Table[Price] ),
Table[Your Lower Level Date Column] = MaxDate
)
The variable finds the maximum date in the context and then we take the price on that date. At the lowest date level the maximum date will always be the date itself and in the subtotal the variable.
Let me know how you get on.
Kris
I'm getting this error - seems this might not work with Direct Queries? Do you know if there is a way around that?
"'CALCULATE' is not allowed as part of calculated column DAX expression on DirectQuery modes."
Hi @tylerwws,
You need to create a new measure not a new column to do this. There's large restrictions on the functions you can use in a calculted column when using Direct Query but using CALCULATE in a measure is definitely allowed.
Let me know how you get on!
Kris