Extract unique value from column using DAX

Jorsch

New Member
Joined
Oct 16, 2019
Messages
1
I've come across what I thought should be a simple problem, but I can't quite figure it out. I have a table that's the result of an expression that could have a column like this in certain instances:

Row Key Index
AH40001
AH40002
AP99993

<tbody>
</tbody>

What I want is to keep only rows with a unique row key so that I can get the correct single index value (the red value). Using distinct/values wouldn't help because it would still show the 1st or 2nd row in the table, where as I want those rows that contain a row key that exists in the table more than once to be fully excluded. I can't rely on position of the index because it varies, so I don't think min/max would work. I have this calculation as part of a complicated calculated column so there are memory concerns as well. Is there an easy answer to this that I'm missing?

Thanks,
Josh
 

macfuller

Board Regular
Joined
Apr 30, 2014
Messages
242
If I understand your question, you want your filter to exclude all rows where the row key appears more than once and to capture the index value. What about a COUNTROWS?
Code:
[Permitted Index] =VAR MyIndex = MyTable[Index]
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( MyTable[Row Key] ),
            FILTER ( ALL ( MyTable ), MyTable[Row Key] = EARLIER ( MyTable[Row Key] ) )
        ) > 1,
        BLANK (),
        MyIndex
    )
Shame on me, I can't remember if the countrows returns the original row too (I think so). If not, you would have to set the test to > 0.
 
Last edited:

Forum statistics

Threads
1,077,650
Messages
5,335,501
Members
399,020
Latest member
julianjjh1

Some videos you may like

This Week's Hot Topics

Top