Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

One Slicer that filters Multiple Columns

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

 

Here is some sample data:

NameSegmentValueDept view indSegment view ind
JohnX10011
SamY7010
RachelW5001
RamZ1000

 

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

Need Help
  • Kaly's avatar
    Kaly
    4 years ago

    Hi Anonymous ,

    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.

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous , 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-power-bi

     

    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

  • Sudhir44's avatar
    Sudhir44
    New Member
    4 years ago
    Spoiler
    Hi Anonymous 
    Below solution will help you getover your problem
    Table:A
    JohnX10011Dept_View
    SamY7010Dept_View
    RachelW5001 
    RamZ1000 

    Table:B
    JohnX10011Segment_View
    SamY7010 
    RachelW5001Segment_View
    RamZ1000 

    Final Table creation by Union function:
    Data_final = UNION(Table_Dept_view,Table_Segment_view)
  • Kaly's avatar
    Kaly
    Icon for Resolver II rankResolver II
    4 years ago

    Hi Anonymous ,

    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.