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!

     

amitchandak's avatar
amitchandak
Super User
4 years ago

Anonymous , Try a measure like this with ID


measure =
var _1 = summarize(filter(all(Table), Table[Type] in allselected(Table[Type] )), Table[ID])
return
countrows(Filter(all(Table) , Table[ID] in _1))

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi amitchandak Thanks for your response. The measure returns total number of rows. Also, I select an ID, it does't return the types similar to the type for that ID.

    • 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.