Need help finding records in a column matched with more than one other record in a different column

[PShpav]

I'm relatively new to DAX and I need to find some kind of COUNTIFS-like formula to produce a column that will help me find if one participant in the Full_Names column has attended more than one series in the Topic column. Basically, I want to replicate something like what you see in the far right column you see below. 

Any help with respect to this question would be greatly appreciated. Thanks!

[edhans]

Hi @PShpav - I created this in Power Query when you first bring the data in. It gets treated as a native column and can perform better than a calculated column in DAX. The other option is a measure as @dax suggested. It turns this:

Into this:

This is the code I used. It was a simple Group By using the Count of Distinct values.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCshMzMtX0lHySk1LU4rViVYKy8zPycwDivgmFmWDRWBKgkuLE/NQ1SCEQopKcwtSS5D1oauKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Topic = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Topic", type text}, {"Name", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {{"Count of Topics", each Table.RowCount(Table.Distinct(_)), type number}, {"All Rows", each _, type table [Topic=text, Name=text]}}),
    #"Expanded All Rows" = Table.ExpandTableColumn(#"Grouped Rows", "All Rows", {"Topic"}, {"Topic"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded All Rows",{"Topic", "Name", "Count of Topics"})
in
    #"Reordered Columns"

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.

[PShpav]

Thank you for taking the time to work on this! Yours is not quite the solution I was looking for but it certainly is good to know that Power BI has such an advanced Power Query feature. I will keep your solution in mind for future needs. 🙂

[dax · Accepted solution]

Hi @PShpav , 

You could try below expression in measure or calculated column 

Column = CALCULATE(DISTINCTCOUNT(t2[Topic]), ALLEXCEPT(t2,t2[Full_Name]))

Best Regards,
Zoe Zhi

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

[PShpav]

Thanks so much! You helped a great deal with what I needed to accomplish!