Forum Discussion

Andy6001's avatar
Andy6001
Frequent Visitor
7 years ago
Solved

DAX

Hi, I'm new to DAX and I'm struggling to understand the following:

 

I have two columns in the same table:

CID (a unique card ID)

UID (a unique user id)

 

I want to evaluate all instances where a uniquie UID has more than one CID.

In a pivot table this would be straight fiorward but I'm completely stumped in Power BI.

 

I can do this in  standard table but I would like it as a measure that I can then stitch into a wider measure.

 

Can anyone help?

Thanks,

Andy

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    Hi Andy6001 ,

     

    Suppose this is your sample table.

     

    To display those records where each unique UID has more than one CID and count the number of UID which meets such a condition, please refer to below measures:

    count CID =
IF (
    CALCULATE ( DISTINCTCOUNT ( Sheet9[CID] ), ALL ( Sheet9[CID] ) ) > 1,
    CALCULATE ( DISTINCTCOUNT ( Sheet9[CID] ), ALL ( Sheet9[CID] ) ),
    BLANK ()
)

Count UID =
IF (
    [count CID] = BLANK (),
    BLANK (),
    CALCULATE (
        DISTINCTCOUNT ( Sheet9[UID] ),
        FILTER ( ALLSELECTED ( Sheet9 ), [count CID] > 1 )
    )
)

    Best regards,

    Yuliana Gu

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi Andy6001 ,

     

    Suppose this is your sample table.

     

    To display those records where each unique UID has more than one CID and count the number of UID which meets such a condition, please refer to below measures:

    count CID =
IF (
    CALCULATE ( DISTINCTCOUNT ( Sheet9[CID] ), ALL ( Sheet9[CID] ) ) > 1,
    CALCULATE ( DISTINCTCOUNT ( Sheet9[CID] ), ALL ( Sheet9[CID] ) ),
    BLANK ()
)

Count UID =
IF (
    [count CID] = BLANK (),
    BLANK (),
    CALCULATE (
        DISTINCTCOUNT ( Sheet9[UID] ),
        FILTER ( ALLSELECTED ( Sheet9 ), [count CID] > 1 )
    )
)

    Best regards,

    Yuliana Gu