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
)

     

     

DataInsights's avatar
DataInsights
Icon for Super User rankSuper User
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
)

 

 

fever003's avatar
fever003
Frequent Visitor
5 years ago

Thank you! This is the solution I needed because it works with Filter context and I can add many other slicers to it. You're the best!