Forum Discussion

adawo_'s avatar
adawo_
New Member
3 years ago
Solved

Filter all rows related to same ID based on criteria

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: 

IDTeam
1TeamA
1TeamB
1TeamC
2TeamB
2TeamC
3TeamA
3TeamC

 

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! 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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.

1 Reply

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.