If Statement in DAX

[siddhantk989]

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.

[parry2k · Accepted solution]

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]

 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]

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

[parry2k]

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

[parry2k · Accepted solution]

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]

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

[parry2k]

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

Cheers!

[siddhantk989]

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

[parry2k · Accepted solution]

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

[siddhantk989]

It is working completely fine. Thanks again