Forum Discussion
Helper IIIvar return with Slicers.
Hi.
There is a wall that I am facing, I tried to find a solution in your youtube video of Power BI contents creators but not yet having any solution.
Please review my problem below and make a contents explaining the problem and giving solutions to subscribers.
I made a measure.
Quantity sold =
var Quantity = SUMX( sales, sales[quantity])
var QuantityYTD = CALCULATE( Quantity, DATESYTD( date[date] ) )
var ProductFilter = FILTER( product, product[code] = "A")
return
CALCULATE( QuantityYTD, ProductFilter)
I think the result of the measure should work with Slicers 'Year', 'Month', 'Product[code]'.
For example, if I select Year Slicer = 2019, Month Slicer = Mar, Product[code] Slicer = C (not A)
I made a list of customer or product, and put the measure.
I suppose the value the measure present should be 0.
The sum of quantity from Jan to Mar 2019, Product[code] = C should be 0.
Because the ProductFilter in the measure is already defined as 'A'.
But this measure presents the actual quantity of Product[code] = C.
What is the problem with my measure...?
Can you help me?
Thanks in advance.
Right but you are still using VAR's and CALCULATE weird. Try this:
Quantity sold = CALCULATE( SUMX( sales, sales[quantity]), DATESYTD( date[date] ), FILTER( product, product[code] = "A") )The other option would be to take your 2 VAR's and turn them into their own measures, then what you are doing with the CALCULATE would make more sense.
8 Replies
- amitchandak
Super User
The only thing I can recommend now is var ProductFilter = FILTER(all( product), product[code] = "A")
If possible please share a sample pbix file after removing sensitive information.Thanks.
Proud to be a Datanaut My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Young_G_HanThank you for your advice.
But All filter is not working.......
- Greg_Deckler
Community Champion
I've never seen a FILTER clause used as a variable and then passed into a CALCULATE function. That's weird and I'm kind of surprised it works. Try taking your FILTER clause for ProductFilter and replace ProductFilter in your RETURN statement with the actual FILTER clause and see if that works.
- Young_G_Han
Thank you for your advice.
But it is not working....
After change the dax formula as following, Change slicer for product[code] <> A presents values....
I must be 0 or empty...
Quantity sold =
var Quantity = SUMX( sales, sales[quantity])
var QuantityYTD = CALCULATE( Quantity, DATESYTD( date[date] ) )return
CALCULATE( QuantityYTD, FILTER( product, product[code] = "A"))- Greg_Deckler
Right but you are still using VAR's and CALCULATE weird. Try this:
Quantity sold = CALCULATE( SUMX( sales, sales[quantity]), DATESYTD( date[date] ), FILTER( product, product[code] = "A") )The other option would be to take your 2 VAR's and turn them into their own measures, then what you are doing with the CALCULATE would make more sense.
