Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Filter table based on selected value

All,   I have a slicer on a Id, type field. User will type in a Id using a Text filter. Depending on the type associated to that id, I need to show all the Id's with that type.   Example: Id   T...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    4 years ago

    Hi  Anonymous ,

     

    If you wanna show all the related rows including the selected one,using below dax expression:

    Measure =
VAR _type =
    CALCULATETABLE (
        VALUES ( 'Table'[ Type] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Id ] IN FILTERS ( 'Slicer table'[Id ] ) )
    )
VAR _id =
    CALCULATETABLE (
        VALUES ( 'Table'[Id ] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ Type] IN _type )
    )
VAR _id2 =
    CALCULATETABLE (
        VALUES ( 'Slicer table'[Id ] ),
        ALLSELECTED ( 'Slicer table' )
    )
VAR _id3 =
    EXCEPT ( _id, _id2 )
RETURN
    IF ( MAX ( 'Table'[Id ] ) IN _id, 1, BLANK () )

    And you will see:

    If you only wanna show other related rows,not including the seleted one,using below dax expression:

    Measure2 =
VAR _type =
    CALCULATETABLE (
        VALUES ( 'Table'[ Type] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Id ] IN FILTERS ( 'Slicer table'[Id ] ) )
    )
VAR _id =
    CALCULATETABLE (
        VALUES ( 'Table'[Id ] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ Type] IN _type )
    )
VAR _id2 =
    CALCULATETABLE (
        VALUES ( 'Slicer table'[Id ] ),
        ALLSELECTED ( 'Slicer table' )
    )
VAR _id3 =
    EXCEPT ( _id, _id2 )
RETURN
    IF ( MAX ( 'Table'[Id ] ) IN _id3, 1, BLANK () )

     

     

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!

     

v-kelly-msft's avatar
v-kelly-msft
Community Support
4 years ago

Hi Anonymous ,

 

Create a measure as below:

Measure =
VAR _type =
    CALCULATETABLE (
        VALUES ( 'Table'[ Type] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Id ] IN FILTERS ( 'Slicer table'[Id ] ) )
    )
VAR _id =
    CALCULATETABLE (
        VALUES ( 'Table'[Id ] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ Type] IN _type )
    )
VAR _id2 =
    CALCULATETABLE (
        VALUES ( 'Slicer table'[Id ] ),
        ALLSELECTED ( 'Slicer table' )
    )
VAR _id3 =
    EXCEPT ( _id, _id2 )
RETURN
    CONCATENATEX ( _id3, [Id ], "," )

And you will see:

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

Anonymous's avatar
Anonymous
Not applicable
4 years ago

v-kelly-msft This solution is perfect. I have a question regarding the last step. I actually wanted it to bring the entire table of 40 columns where there is a match, instead of just showing the concatenated results.

 

I was not able to return the measure in a way where I can map it to the original table and bring only rows which matches to it. Can you let me know how I can resolve it, please?

 

Thank You

V.

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    4 years ago

    Hi  Anonymous ,

     

    If you wanna show all the related rows including the selected one,using below dax expression:

    Measure =
VAR _type =
    CALCULATETABLE (
        VALUES ( 'Table'[ Type] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Id ] IN FILTERS ( 'Slicer table'[Id ] ) )
    )
VAR _id =
    CALCULATETABLE (
        VALUES ( 'Table'[Id ] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ Type] IN _type )
    )
VAR _id2 =
    CALCULATETABLE (
        VALUES ( 'Slicer table'[Id ] ),
        ALLSELECTED ( 'Slicer table' )
    )
VAR _id3 =
    EXCEPT ( _id, _id2 )
RETURN
    IF ( MAX ( 'Table'[Id ] ) IN _id, 1, BLANK () )

    And you will see:

    If you only wanna show other related rows,not including the seleted one,using below dax expression:

    Measure2 =
VAR _type =
    CALCULATETABLE (
        VALUES ( 'Table'[ Type] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Id ] IN FILTERS ( 'Slicer table'[Id ] ) )
    )
VAR _id =
    CALCULATETABLE (
        VALUES ( 'Table'[Id ] ),
        FILTER ( ALL ( 'Table' ), 'Table'[ Type] IN _type )
    )
VAR _id2 =
    CALCULATETABLE (
        VALUES ( 'Slicer table'[Id ] ),
        ALLSELECTED ( 'Slicer table' )
    )
VAR _id3 =
    EXCEPT ( _id, _id2 )
RETURN
    IF ( MAX ( 'Table'[Id ] ) IN _id3, 1, BLANK () )

     

     

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my reply as a solution!