Forum Discussion

siddhantk989's avatar
siddhantk989
Icon for Helper III rankHelper III
9 years ago
Solved

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...
Need Help
Tips and Tricks
  • parry2k's avatar
    parry2k
    9 years ago

    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. 

  • parry2k's avatar
    parry2k
    9 years ago

    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())
parry2k's avatar
parry2k
Icon for Super User rankSuper User
9 years ago
Away from desk, not tested the formula. Will take a look as soon back at my desk.
parry2k's avatar
parry2k
Icon for Super User rankSuper User
9 years ago

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. 

  • siddhantk989's avatar
    siddhantk989
    Icon for Helper III rankHelper III
    9 years ago

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

    • parry2k's avatar
      parry2k
      Icon for Super User rankSuper User
      9 years ago

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

       

      Cheers!

      • siddhantk989's avatar
        siddhantk989
        Icon for Helper III rankHelper III
        9 years ago

        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