cancel
Showing results for
Did you mean:
Highlighted
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"
)```

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.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User VII

## Re: If Statement in DAX

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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Super User VII

## Re: If Statement in DAX

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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

9 REPLIES 9
Super User VII

## Re: If Statement in DAX

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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Helper III

## Re: If Statement in DAX

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

Highlighted
Super User VII

## Re: If Statement in DAX

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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Super User VII

## Re: If Statement in DAX

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.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Helper III

## Re: If Statement in DAX

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

Highlighted
Super User VII

## Re: If Statement in DAX

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

Cheers!

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Helper III

## Re: If Statement in DAX

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.

Regards,

Siddhant

Highlighted
Super User VII

## Re: If Statement in DAX

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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Highlighted
Helper III

## Re: If Statement in DAX

It is working completely fine. Thanks again

Announcements

#### Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

#### Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors