Measure to identity values in one column which have multiple values in another column

[tgjones43]

Hi all

I need a measure to return True if there is more than one unique value for each unique value in another column. So in the below example, it should return False for value 1 in ID1 (as there is only 1 unique value in ID2) but True for value 2 in ID1 (because there are 2 unique values in ID2).

ID1	ID2	Value
1	A	1
1	A	2
1	A	3
2	A	1
2	A	2
2	A	3
2	B	1
2	B	2
2	B	3

Thank you!

[rsbin · Accepted solution]

@tgjones43 ,

Hope this Measure works for you:

Unique = IF( CALCULATE( DISTINCTCOUNT( 'Table'[ID2] ),
                 ALLEXCEPT( 'Table','Table'[ID1] )) = 1, 
                 "False", "True" )

Regards,

[petrawiggin]

UPDATE: I forgot that my file was a csv, which was preventing me from adding measures. 😅

However, I'm not sure that it's working right now that I was able to create it. What is the way that this could be created as a calculated column, @tgjones43 ?

----

Hello. I think this solution is just what I need for my own data except I don't understand where to put this function. I'm sure this is very basic information but can you please demonstrate where I put this? My data is a table loaded from a power query query.

When I try to put it in as a step in my query I get an error "Expression.Error: The name 'IF' wasn't recognized. Make sure it's spelled correctly." If I change "IF" to "if" I get an error saying then is expected, then else is expected then "Token Eof expected". If I then remove the ) at the end it says "CALCULATE" isn't recognized.
I get the same when I try to make this a function. 

If this is supposed to be a pivot table measure, I can't figure out how to get a measure in (the options under power pivot are always all greyed out, even when I add a pivot table). 

Any help would be apprectiated.

[rsbin]

@petrawiggin ,

This was originally created as a DAX Measure.

What you are doing above is trying to use DAX as an M Query in PQ.

Once you have loaded all of your data, go to your Power BI report  and create a "New Measure.

Hope this helps.

Regards,

[tgjones43]

@rsbin it does, thank you! As a calculated column, rather than a measure, but that's fine.