cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

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

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors