Forum Discussion

siddhantk989's avatar
siddhantk989
Helper 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 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"
)

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.

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

9 Replies

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