Need a Measure or Calculated Column for Visual

[cmcgo3]

This is probably an easy fix, but I cannot figure out if I need a calculated column or DAX measure.  I do not get the result I want when applying a filter in visual (clustered bar chart).

I have Player 'A' and Player 'B' that participated in both Year 2022 and 2023.  Players 'C' only participated in 2023, while Player 'D' only particpated in 2022.  

I need a filter that excludes Players 'C' and 'D' because I only want to show players that particpated in both 2022 and 2023 in the visual.

  filter.pbix 

[AndrewPF]

There may be easier ways but this is my first thought. 

In Power Query, create a new table from your original data (use "Reference"). 

Add an index (this is only so that the group by function retains the year order). 

Group by Player ID and summarise Year (use Text.Combine function - you will have to edit the Group by AFTER you have created it): 

let 

Source = Table,
#"Changed Type" = Table.TransformColumnTypes(Source,{{"year", type text}}),
#"Removed Duplicates" = Table.Distinct(#"Changed Type", {"year", "player"}),
#"Sorted Rows" = Table.Sort(#"Removed Duplicates",{{"player", Order.Ascending}, {"year", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
#"Grouped Rows" = Table.Group(#"Added Index", {"player"}, {{"Years", each Text.Combine([year], "; "), type nullable text}})
in
#"Grouped Rows"

Now go to Model View, and create a relationship on Player ID between your new and original tables (it may already do it - but you need to decide whether you want the relationship to be one-way or both ways). 

The either add a new slicer with the new combined year variable from the new table, or add a visual-level or page-level filter. 

[cmcgo3 · Accepted solution]

Awesome! That worked for me!  I appreciate the help...