Forum Discussion
Power Pivot Filters
Yes, small tweaks, but instead of one measure it is now 4, so rather than post them all here, just grab the PBIX file. It returns a table like this:
I appreciate the candle offer, but I'm just here to help. 🙂
Hi SamTaylor , thank you for the sample data. I wrote a measure that returns this, which is the quantity for the last receipt date of a product by location. Given that your quantites for UK and US are always the same, it is hard to see that my measure works though i this screen shot.
Last Qty and Date by Location =
VAR varCurrentLocation =
MAX( 'Table'[Location] )
VAR varCurrentProduct =
MAX( 'Table'[Product] )
VAR varLastDateByLocation =
MAXX(
FILTER(
ALL( 'Table' ),
'Table'[Location] = varCurrentLocation
&& 'Table'[Product] = varCurrentProduct
),
'Table'[Receipt Date]
)
VAR Result =
CALCULATE(
SUM( 'Table'[Qty] ),
FILTER(
ALL( 'Table' ),
'Table'[Location] = VarCurrentLocation
&& 'Table'[Product] = varCurrentProduct
&& 'Table'[Receipt Date] = varLastDateByLocation
)
)
RETURN
Result
OMG, it's excellent. I thought I might be able to pick up some Dax learning on the side but it's way above me.
I have applied it to the test table below and the result is in the pivot below that. (see image)
But now I am fealing guilty as I need a minor amend, due to my lack of clarity at the start.
I need the UK and US results to be in separate measures, as in the green table. Is this a quick change?
Also, my wife makes candles, can I send you one as a gift?
see www.wrightandgreen.co.uk for a selection of scents.
Thanks
Sam
Yes, small tweaks, but instead of one measure it is now 4, so rather than post them all here, just grab the PBIX file. It returns a table like this:
I appreciate the candle offer, but I'm just here to help. 🙂