Filter table based on selected value

[Anonymous]

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

[amitchandak]

@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]

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]

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]

@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 · Accepted solution]

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!