CODEWITHSUNDEEP
sql-serverpowerbi
DenStudent
DenStudent

Reputation: 928

Slicer to include 2 columns at once

So I have a view like this in SQL Server which I'm using to build a dashboard in Power BI:

ID | Name | IsRegional | IsFederal | Department | ... 
1  | John | Yes        | No        | Paris      | ...
2  | Mike | No         | Yes       | Brussels   | ...
3  | Bill | No         | Yes       | Berlin     | ...
4  | Bart | Yes        | Yes       | Berlin     | ...
5  | Suzy | Yes        | No        | New York   | ...

Currently I have 2 slicers in PowerBi that say "Is Regional: Yes/no" and "Is Federal: Yes/no". I want to make one slicer of this saying "Type: Federal/Regional"

My idea was to add a column TYPE in the view that says

WHEN IsRegional = 'Yes' THEN 'Regional'
WHEN IsFederal = 'Yes' THEN 'Federal'
ELSE 'None'

and then use the new column for the slicer

ID | Name | IsRegional | IsFederal | Type     | Department | ... 
1  | John | Yes        | No        | Regional | Paris      | ...
2  | Mike | No         | Yes       | Federal  | Brussels   | ...
3  | Bill | No         | Yes       | Federal  | Berlin     | ...
4  | Bart | Yes        | Yes       | Regional | Berlin     | ...
5  | Suzy | Yes        | No        | Regional | New York   | ...

However, this creates an issue with record 4 where the type can be both. I would like the slicer to include row 4 when I have Federal selected as type (since it's both federal as Regional). Is there a way to solve this issue so I can use the single slicer? I would rather not add a 4th option saying "Both" to the slicer because I'm sure people will look over that one.

Upvotes: 1

Views: 46

Answers (1)

Thom A
Thom A

Reputation: 95561

Just change your CASE expression slightly:

CASE WHEN IsRegional = 'Yes' AND IsFederal = 'Yes' THEN 'Both'
     WHEN IsRegional = 'Yes' THEN 'Region'
     WHEN IsFederal = 'Yes' THEN 'Federal'
     ELSE 'None'
END

Upvotes: 1

Related Questions