Filtering column A based on the contents of column B

[dw700d]

I have a list of reference numbers. Each reference number is composed of two transactions. In some instances a reference number contains the same location and other instances the reference number contains two separate locations. I would like to create a measure that helps me identify reference numbers that contain 2 separate locations. In the example below the measure would identify reference number 101 because it contains to different locations

Reference#	Transaction	Location
100	1	NY
100	2	NY
101	1	DC
101	2	ATL
102	1	LA
102	2	LA
103	1	NC
103	2	NC

[parry2k · Accepted solution]

@dw700d add a measure and you can filter on this measure where value is > 1

Measure = CALCULATE ( DISTINCTCOUNT ('Table'[Location] ), ALLEXCEPT ('Table','Table'[Reference] ) ) 

✨ Follow us on LinkedIn and  to our YouTube channel

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) 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.

[freginier]

You can create calculated column to do that 

Col = CALCULATE( DISTINCTCOUNT('Table'[Location]),FILTER('Table',EARLIER('Table'[Reference])='Table'[Reference]))

Then filter on greater than 1