One Slicer that filters Multiple Columns

[shephaliB]

Hi, How do I create one slicer based on multiple columns that can help me filter rows?

Here is some sample data:

Name	Segment	Value	Dept view ind	Segment view ind
John	X	100	1	1
Sam	Y	70	1	0
Rachel	W	50	0	1
Ram	Z	10	0	0

In the slicer drop down, I need "dept View" and "Segment View". 

When I select "Dept View", This should filter "Dept view ind"=1 and when I select "Segment View", This should filter "Segment view ind"=1

Slicer >

Dept View

Segment View

Output for Segment view:

Value = 100+50 = 150

Output for Dept view:

Value = 100+70 = 170

Thanks,

Shephali

[Kaly · Accepted solution]

Hi @shephaliB ,

According to your description, I create a sample:

Table:

Slicer table:

Here's my solution, create a measure:

Measure =
IF (
    ISFILTERED ( Slicer[Slicer] ),
    IF (
        SELECTEDVALUE ( Slicer[Slicer] ) = "Dept view"
            && MAX ( 'Table'[Dept view ind] ) = 1,
        1,
        IF (
            SELECTEDVALUE ( Slicer[Slicer] ) = "Segment view"
                && MAX ( 'Table'[Segment view ind] ) = 1,
            1
        )
    ),
    1
)

Put the measure in the visual filter and select its value to1

Get the result:

Best Regards,

Kaly

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

[Sudhir44]

Spoiler

Hi @shephaliB 
Below solution will help you getover your problem
Table:A

John	X	100	1	1	Dept_View
Sam	Y	70	1	0	Dept_View
Rachel	W	50	0	1
Ram	Z	10	0	0

Table:B

John	X	100	1	1	Segment_View
Sam	Y	70	1	0
Rachel	W	50	0	1	Segment_View
Ram	Z	10	0	0

Final Table creation by Union function:

Data_final = UNION(Table_Dept_view,Table_Segment_view)

[amitchandak]

@shephaliB , You can create a table with distinct values what have in these two columns, and then you join both of them with that table, one join will inactive that you can active using userelationship

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...

The second option is that, keep the table independent and pass value filter using treatas

https://docs.microsoft.com/en-us/dax/treatas-function

Need of an Independent Table in Power BI - Exclude: https://youtu.be/lOEW-YUrAbE