Create filter to return rows that match in one column and differ in another

[Anonymous]

Hi all, 

My problem is thus: I have a column with addresses (we'll call it "Location") and a column with values A or B (we'll call it "Letter"). I need to return rows where, for the same value in Location, there are both rows with A, and some with B. 

Here's an example. I'm using a column called "Flag" in this case as my intention for filtering, where I would include "True" in a visual and leave "False" out. Perhaps a measure would be better, but I don't know, yet. 

Location	Letter	Flag
California	A	True
New York	B	False
Wyoming	A	False
California	B	True
New York	B	False

So, because there are rows for Location = California where Letter = A and rows for Location = California where Letter = B, I want to include those California rows. For the others, (Wyoming is always A, New York is always B) I would not return those rows/locations. Note that I don't know which Locations will return "True", so I don't want to write logic that is specific to strings "California", "Wyoming", "New York"... it should work for any Location where the associated Letter is sometimes A and sometimes B. 

What is the most efficient way for me to do this? Even if it's not necessary to create a new column, all I really want to do is filter a chart for those 'Locations' where multiple 'Letters' are associated. 

Thanks! 

[parry2k · Accepted solution]

@Anonymous try this measure

Measure = 
VAR __tbl = CALCULATETABLE ( VALUES ( Location[Letter] ), ALLEXCEPT ( Location, Location[Location] ) )
RETURN
IF ( COUNTROWS ( __tbl ) >= 2, 1,  0 )

Check my latest blog post Compare Budgeted Scenarios vs. Actuals to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[Anonymous]

This works great, thank you!!