Two Slicers to multiple columns of one table

[Anonymous]

Hi,

I would to add two slicers to my dashboard which will be filtering table as rules below:
Slicer1 - filter columns 'A Rack' and 'B Rack'

Slicer2 - filter columns 'A Equip' and 'B Equip'

If I will select value in slicer1 then table need to show all rows if have values in column 'A Rack' or/and in column 'A Rack', slicer2 dropdown list will be filtered also. Same functionality if I will select value in Slicer2.

I tried this solution Solved: Once Slicer for multiple columns - Microsoft Power BI Community  but it's working for one slicer only.

My data table have few thousends of rows with connections from A end to B end on different sites. Example of my table:

Site name	A Rack	A Equip	B Rack	B Equip	Length
site A	rack A1	device 1	rack B1	device 22	2
site A	rack A1	device 1	rack B1	device 22	2
site A	rack A2	device 1	rack B2	device 33	2
site A	rack A3	device 4	rack B2	device 33	4
site A	rack B1	device 2	rack B2	device 33	6
site A	rack B1	device 2	rack B2	device 33	6
site A	rack B2	device 3	rack A3	device 4	8
site A	rack B3	device 3	rack A3	device 4	8
site A	rack B1	device 3	rack A1	device 1	10
site A	rack B1	device 3	rack A1	device 1	10
site B	rack A1	device 11	rack B1	device 22	11
site B	rack A1	device 11	rack B1	device 22	12
site B	rack A2	device 11	rack B2	device 3	13
site B	rack A3	device 4	rack B2	device 3	14
site B	rack B2	device 2	rack B2	device 3	15
site B	rack B2	device 2	rack B2	device 3	15
site B	rack B2	device 3	rack A2	device 41	17
site B	rack B3	device 13	rack A3	device 14	18
site B	rack B4	device 3	rack A1	device 11	20
site B	rack B4	device 3	rack A1	device 11	20

Will be it possible to implement?

Thanks in advance 🙂

[v-xulin-mstf · Accepted solution]

Hi @Anonymous,

 Create two slicer table as:

Slicer1 = 
DISTINCT(
    UNION(
        VALUES('Table'[A Rack]),
        VALUES('Table'[B Rack])
    ) )

Slicer2 = 
DISTINCT(
    UNION(
        VALUES('Table'[A Equip]),
        VALUES('Table'[B Equip])
    ) )

Create two measures as:

Measure = 
IF(
    MAX(Slicer1[A Rack])=MAX('Table'[A Rack])||MAX(Slicer1[A Rack])=MAX('Table'[B Rack]),
    1,
    0)

Measure 2 = 
IF(
    MAX(Slicer2[A Equip])=MAX('Table'[A Equip])||MAX(Slicer2[A Equip])=MAX('Table'[B Equip]),
    1,
    0)

Drag the measures into filter as:

Please try this demo and check if it works.

Best Regards,

Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This do the job. I modified a bit a measure to show all in case no value in slicer - added ISFILTERED.

Measure 2 = 
IF(
    ISFILTERED(Slicer2[A Equip]),
    IF(
        MAX(Slicer2[A Equip])=MAX('Table'[A Equip])||MAX(Slicer2[A Equip])=MAX('Table'[B Equip]),
        1,
        0),
    1)

 Will be possible to make it working for multiple selections? I would to use visual Smart Filter by OKVIZ or maybe you know a better visual for that? The important think for me is to have possibility to start typing value.

[v-xulin-mstf]

Hi @Anonymous,

You can use similar methods to create more dimensional tables to make it working for multiple selections.

The visual Smart Filter by OKVIZ is really a good choice！

Best Regards,

Link

[amitchandak]

@Anonymous , You have 4 independent table. 2 for each case ? Then only it will work

[Anonymous]

@amitchandak thanks for quick response.

I have all in one table, so I need to split columns and generate some id column to be used to connect splitted ones. Not sure if this will have impact on performence of dashboard. I will try.