Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
siddhantk989
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

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. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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
parry2k
Super User
Super User

 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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

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

 

Cheers!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

It is working completely fine. Thanks again

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.