topic Re: Measure to identity values in one column which have multiple values in another column in DAX Commands and Tips

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

tgjones43 — Tue, 25 Jun 2024 14:05:52 GMT

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!

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

rsbin — Tue, 25 Jun 2024 15:23:23 GMT

tgjones43 ,

Hope this Measure works for you:

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

Regards,

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

tgjones43 — Tue, 25 Jun 2024 16:08:27 GMT

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

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

petrawiggin — Sat, 10 Aug 2024 00:36:49 GMT

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.

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

rsbin — Tue, 13 Aug 2024 12:58:30 GMT

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,