cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

If Statement in DAX

Hi,

 

  I am generating  a custom filter based on my column values Sales and Units. So for this I am using IF statement to select between Sales and Units. Now it is working fine if the user selects either Sales or Units to filter but I am not getting any value if the user does not selects any of the 2. What I want is that the graph should display data for both Sales and Units if the user selects nothing in the filter. Below is the code and dummy table:

 

DisplayValue =
IF( HASONEVALUE ( DisplayBy[DisplayBy] ),
  IF( VALUES( DisplayBy[DisplayBy] ) = "Sales",
    SUM( Table1[total Sales] ),
    SUM (Table1[Total Units] )
  ),
  "Please select only one measure to display from the slicer"
)

small.png

So instead of "Please select only one measure to display from the slicer"  I need to display data for both Sales and Units based on years.

 

Also I can not unpivot the data as the tables are dynamic tables that are generated on different logics. So combining them and then using is not an option.

 

Thanks in advance.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: If Statement in DAX

Here are revised formula:

 

Display Quantity = if(OR(hasonevalue('Show Data By'[Show Data])=False ,values('Show Data By'[Show Data])="Qty"), SUM(Sales[Quantity]), BLANK())

Display Revenue = if(OR(hasonevalue('Show Data By'[Show Data])=False ,values('Show Data By'[Show Data])="Revenue"), SUM(Sales[Revenue]), BLANK())

 

 

I used by own data coloumn, you can change it to your own. I tested at my end it works, if need with your own columns, i will do it for you. 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Super User IV
Super User IV

Re: If Statement in DAX

I hope this will work:

 

Display Quantity = if(contains('Show Data By','Show Data By'[Show Data],"Qty"), SUM(Sales[Quantity]), BLANK())

Display Revenue = if(contains('Show Data By','Show Data By'[Show Data],"Revenue"), SUM(Sales[Revenue]), BLANK())





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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

9 REPLIES 9
Highlighted
Super User IV
Super User IV

Re: If Statement in DAX

 Does it make sense to create two calculations one for sales and one for units and drop both of those on the report.

 

DisplaySalesValue =
  IF( VALUES( DisplayBy[DisplayBy] ) = "Sales",
    SUM( Table1[total Sales] ),
    BLANK())
  )
DisplayUnitValue =
  IF( VALUES( DisplayBy[DisplayBy] ) = "Unit",
    SUM( Table1[total Unit] ),
    BLANK())
  )

 

I believe this will work.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper III
Helper III

Re: If Statement in DAX

Using this is giving me an error that "A table of multiple values was supplied where a single value was expected". 

Highlighted
Super User IV
Super User IV

Re: If Statement in DAX

Away from desk, not tested the formula. Will take a look as soon back at my desk.





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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Super User IV
Super User IV

Re: If Statement in DAX

Here are revised formula:

 

Display Quantity = if(OR(hasonevalue('Show Data By'[Show Data])=False ,values('Show Data By'[Show Data])="Qty"), SUM(Sales[Quantity]), BLANK())

Display Revenue = if(OR(hasonevalue('Show Data By'[Show Data])=False ,values('Show Data By'[Show Data])="Revenue"), SUM(Sales[Revenue]), BLANK())

 

 

I used by own data coloumn, you can change it to your own. I tested at my end it works, if need with your own columns, i will do it for you. 






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Helper III
Helper III

Re: If Statement in DAX

The solution is working fine. thansk a lot @parry2k for helping me out with this.

Highlighted
Super User IV
Super User IV

Re: If Statement in DAX

Glad to hear and feel free to reach out to community for more questions.

 

Cheers!






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper III
Helper III

Re: If Statement in DAX

Hi @parry2k.

 

Need one last suggestion.  Just now what we did was that if user was not selecting any value in the filter. What changes should I make in my code that if user selects both of the filters I should gata data for both sales and units?  Right now if I select both Sales and Units in my code it is giving me the same error.

 

Thanks in advance.

 

Regards,

Siddhant

Highlighted
Super User IV
Super User IV

Re: If Statement in DAX

I hope this will work:

 

Display Quantity = if(contains('Show Data By','Show Data By'[Show Data],"Qty"), SUM(Sales[Quantity]), BLANK())

Display Revenue = if(contains('Show Data By','Show Data By'[Show Data],"Revenue"), SUM(Sales[Revenue]), BLANK())





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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

Highlighted
Helper III
Helper III

Re: If Statement in DAX

It is working completely fine. Thanks again

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors