Search code examples
powerbidaxpowerbi-desktop

DAX For duplicated values in first column, check if there are duplicated values in second column

I wrote a DAX formula which identifies duplicates in column "Number". The new created column has value "YES" or "NO".

Now to the problem I cannot figure out: I need to check for rows with duplicated value in column "Number", do they have differnt value in "Biz Type". "Biz Type" could have only two vaules "SP" and "PC".

The output is in the example below, highlighted in yellow. If there is a duplicate in number and differnce in column "Biz type" result is "No"

Could you help me with the DAX formula. Thank you in advance!

Example

Solution

  • pls try this to create a column

    Column=
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Biz Type] ),
        ALLEXCEPT ( 'Table', 'Table'[Number] )
    )
VAR _count2 =
    CALCULATE ( COUNTROWS ( 'Table' ), ALLEXCEPT ( 'Table', 'Table'[Number] ) )
RETURN
    IF ( _count2 = 1, "N/A", IF ( _count = 1, "YES", "NO" ) )

    enter image description here