Forum Discussion

fever003's avatar
fever003
Frequent Visitor
5 years ago
Solved

Counting only duplicates when condition is TRUE

Hi all,  Hope to get some help here. I have a situation where I only want to count the UniqueID that has duplicates. Right now it is giving me 6 counts where as I expect to get only 4 accounts whe...
Need Help
Show and Tell
  • DataInsights's avatar
    DataInsights
    5 years ago

    fever003,

     

    Try this measure:

     

    Count UniqueID Duplicates = 
SUMX (
    --iterate the distinct UniqueIDs in the filter context
    VALUES ( DuplicateCount[UniqueID] ),
    --current UniqueID
    VAR vUniqueID = DuplicateCount[UniqueID]
    --return the rows for the current UniqueID in the filter context
    VAR vUniqueIDRows = FILTER ( ALLSELECTED ( DuplicateCount ), DuplicateCount[UniqueID] = vUniqueID )
    --if the current UniqueID has more than one row, assign a count of 1
    VAR vUniqueIDCount = IF ( COUNTROWS ( vUniqueIDRows ) > 1, 1 )
    RETURN
        vUniqueIDCount
)

     

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
5 years ago

Here is another similar way to do it.

 

IDs with Duplicates =
COUNTROWS (
    FILTER (
        DISTINCT ( UniqueDuplicates[UniqueID] ),
        CALCULATE (
            COUNTROWS ( UniqueDuplicates ) = 2
        )
    )
)

 

Regards,

Pat

fever003's avatar
fever003
Frequent Visitor
5 years ago

Thanks, this works too!