cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

var 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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: var return with Slicers.

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. 

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
Highlighted
Super User IV
Super User IV

Re: var return with Slicers.

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-tr...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Super User IV
Super User IV

Re: var return with Slicers.

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.


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: var return with Slicers.

Thank you for your advice.

 

But All filter is not working.......

Highlighted
Frequent Visitor

Re: var return with Slicers.

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"))

Highlighted
Super User IV
Super User IV

Re: var return with Slicers.

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. 

 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Highlighted
Frequent Visitor

Re: var return with Slicers.

Yes. It is working.

 

Because I have many similar calculation, I tried to use var - return calculation.

 

I am not familiar with dax yet, I have tried.

 

Can you please advise me that any web page or helpful document that can help me to understand var - return calculation?

Highlighted
Super User IV
Super User IV

Re: var return with Slicers.

A VAR within a DAX calculation is a bit of a misnomer. Once you calculate a VAR, it is static (hence why it is a bit of misnomer). You cannot use CALCULATE to recalculate a VAR. You generally use CALCULATE to calculate a measure or DAX expression with some new filter criteria. A VAR is really useful when you want to break down a complex calculation into manageable chunks or avoid doing the same calculation twice (like in an IF statement). However, the way you were using them is simply invalid. 


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: var return with Slicers.

Thank you. It helps me a lot. I got one step close to DAX formula.

 

Have a good day.

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors