Slicer that checks two columns

[tychi95]

Hello.

I have a table that has two columns I would like to use a slicer for, Primary Capability and Secondary Capability (there are other columns but they are not related to this slicer).

Example table:

Row #	Primary Capability	Secondary Capability
1	Account Management
2	Data Migration	Account Management
3	Cyber Security	Account Management
4	Analytics	Cyber Security
5	Insider Risk

I have a Capabilties table which will provide the slicer with its options. If I select 'All' in the slicer, it should display all rows in the table. If I select one option, it should display rows where the Primary or Secondary Capabilities match (Scenario A). If I select multiple options, it should display rows where the Primary or Secondary Capabilities are any one of the selected options (Scenario B).

Scenario A:

If I select Account Management in the slicer, the following rows should be displayed.

Row #	Primary Capability	Secondary Capability
1	Account Management
2	Data Migration	Account Management
3	Cyber Security	Account Management

Scenario B:

If I select Cyber Security and Insider Risk in the slicer, the following rows should be displayed

Row #	Primary Capability	Secondary Capability
3	Cyber Security	Account Management
4	Analytics	Cyber Security
5	Insider Risk

Thanks in advance!

[Krutigawale33]

Hello @tychi95 

You can refer my pbix file here pbix file 

[Krutigawale33 · Accepted solution]

Hello @tychi95 ,

create a new column in your table I used Sheet1 here

PrimaryCapability|SecondaryCapablility = COMBINEVALUES("|",Sheet1[Primary Capability],Sheet1[Secondary Capability])

and then create new table 
Slicer =
DISTINCT (
UNION (
SELECTCOLUMNS (
Sheet1,
"Selection", Sheet1[Primary Capability],
"Capabilities", Sheet1[PrimaryCapability|SecondaryCapablility]
),
SELECTCOLUMNS (
Sheet1,
"Selection", Sheet1[Secondary Capability],
"Capabilities", Sheet1[PrimaryCapability|SecondaryCapablility]
)

)
)

So create a relationship between this two tables

Sheet1 table, PrimaryCapability|SecondaryCapablility column and Slicer table Capabilities column 

and cross filter direction both

and use selection column in slicer to filter the data.

[mussaenda]

Hi @tychi95,

Is it okay for yo to do a separate table for Primary and secondary Capability?

then create a bridge to connect both of them.

And use that bridge in Slicer.

For sure others have different approach on this also.

But hope this helps.

Thank you!