Forum Discussion

nemo189's avatar
nemo189
Frequent Visitor
2 years ago
Solved

Filtering using slicer dax

I have two disconnected tables table 1 and table 2 . Now I want to create a slicer using table 1(slicer table) column . condition is if nothing is selected then a certain value should be filtered in table 2 . If any other value is selected then corresponding data should be filtered in table 2. Example : if user selects blank in slicer then the table 2 should be default filtered for only D. If user selects any value lets say M, then table 2 should be filtered for M. If user selects ALL , then All the value should be shown . I was thinking on an approach using flag . Not able to achieve. Please help

 

 

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi nemo189 

    You can refer to the following sample.

    Sample table2.

    Slicer table

     

    Create a measure

    Measure =
IF (
    ISFILTERED ( 'Table'[Column1] ),
    IF (
        SELECTEDVALUE ( 'Table'[Column1] ) = "ALL",
        CALCULATE ( SUM ( Table2[Value] ) ),
        CALCULATE (
            SUM ( Table2[Value] ),
            FILTER ( Table2, Table2[Column1] IN VALUES ( 'Table'[Column1] ) )
        )
    ),
    CALCULATE ( SUM ( Table2[Value] ), FILTER ( Table2, Table2[Column1] = "D" ) )
)

    Output

     

     

    Best Regards!

    Yolo Zhu

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

2 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    2 years ago

    Hi nemo189 ,

     

    Ok, so firstly you don't want/need the blank value in Table2 in order to have an unfiltered state, you can just clear the slicer, so remove this from your Table2 options.

    Next, I think the best way to handle your requirements would be via your measure(s), something like this:

    _yourMeasure =
IF(
    ISFILTERED(Table2[slicer table column]),
    [your regular calculation here],
    [your "D" calculation here]
)

     

    Pete

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi nemo189 

    You can refer to the following sample.

    Sample table2.

    Slicer table

     

    Create a measure

    Measure =
IF (
    ISFILTERED ( 'Table'[Column1] ),
    IF (
        SELECTEDVALUE ( 'Table'[Column1] ) = "ALL",
        CALCULATE ( SUM ( Table2[Value] ) ),
        CALCULATE (
            SUM ( Table2[Value] ),
            FILTER ( Table2, Table2[Column1] IN VALUES ( 'Table'[Column1] ) )
        )
    ),
    CALCULATE ( SUM ( Table2[Value] ), FILTER ( Table2, Table2[Column1] = "D" ) )
)

    Output

     

     

    Best Regards!

    Yolo Zhu

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