Filter all rows related to same ID based on criteria

[adawo_]

Hello!

Not sure if I am overthinking it, but I need to filter out all rows related to the same ID, based on a criteria from another column, using a slicer.

Example: 

ID	Team
1	TeamA
1	TeamB
1	TeamC
2	TeamB
2	TeamC
3	TeamA
3	TeamC

Essentially with this example, I would want a slicer with all the Teams and when the user selects TeamA for instance, the only result would be ID 2 (with its other corresponding data).

I would be using this in a bar chart, that counts the ID per team.

Thank you! 

[v-yanjiang-msft · Accepted solution]

Hi @adawo_ ,

According to your description "when the user selects TeamA for instance, the only result would be ID 2", here's my solution.

1.Create a new table with all teams, don't make relationship with the two tables.

Team = VALUES('Table'[Team])

2.Create a measure.

Measure =
IF (
    COUNTROWS (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Team] = SELECTEDVALUE ( Team[Team] )
                && 'Table'[ID] = MAX ( 'Table'[ID] )
        )
    ) > 0,
    1,
    0
)

Put the new created Team table in slicer and columns from fact table in visual, then put the measure in visual filter, select show items when the value is 0.

After apply filters, get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

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