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   Type

1     Dog Cat

2     

3     Dog Cat

4     Horse

5     

6     Dog Cat

7     Dog Cat

 

If some one select Id "3", I need to show, 1, 6 & 7.

 

I tried the following:

 

idSelected = SELECTEDVALUE(table1[Id])

 

TypeToSearch =
LOOKUPVALUE(Table1[Type], Table1[ID], [idSelected], "No Value")
 
identicalKeywords = COUNTROWS(FILTER(Table1, CONTAINS(Table1, Table1[Type], [wordToSearch])))
 
If I replace "wordToSearch" with actual value "Dog Cat" in the last step, it works fine.
 
Any thoughts how I can resolve this?
 
Thank You
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!

     

5 Replies

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