CODEWITHSUNDEEP
excelpowerbidax
Let Soo Gas
Let Soo Gas

Reputation: 77

PowerBi - Dax formula to get multiple same columns on the same table

I'm very new to powerbi and dax and would like to seek some help or suggestions here.

How am I going to return the rows with exactly have match on other columns. I want to return the highlighted data on the picture. Or maybe I can have a new column with value of 1 for duplicate and 0 for not so I can return them all depending on it.

I'm trying to use lookupvalue but I don't know how to do it with multiple columns to return

enter image description here

Upvotes: 0

Views: 894

Answers (1)

smpa01
smpa01

Reputation: 4346

You need to either create a surrogateID on-the-fly and utilize that in the calculation to let DAX return you the duplicates or count the dates by PMT-val partition as in ALLEXCEPT.

Calculated Column - using COUNTX

isDup? =
VAR _surrogateID = 'Table'[PMTNo] & 'Table'[val] & 'Table'[date]
VAR _count =
    CALCULATE ( COUNTX ( 'Table', _surrogateID ) )
RETURN
    IF ( _count > 1, "dup", "unique" )

Calculated Column - using ALLEXCEPT -Preferred Than COUNTX - this doubles as a measure too

isDup2? =
CALCULATE (
    COUNT ( 'Table'[date] ),
    ALLEXCEPT ( 'Table', 'Table'[PMTNo], 'Table'[val] )
)

Calculated column

Measure

Measure =
VAR _surrogateID =
    MAX ( 'Table'[PMTNo] ) & MAX ( 'Table'[val] )
        & MAX ( 'Table'[date] )
VAR _count =
    CALCULATE ( COUNTX ( 'Table', _surrogateID ) )
RETURN
    IF ( _count > 1, "dup", "unique" )

Measure

Upvotes: 2

Related Questions