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

 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.

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

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

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