Search code examples
powerbidaxvisualizationpowerbi-desktopmeasure

Count without duplicates Power BI


I have a table like -

Seller Value
Ron 0
Jon 12
Ron 12
Jon 13
Harry 121
Saw 0

I need to count the seller if its value is not zero and non-duplicate.

Here the seller count would be 2.

Explanation -

Since Ron is available twice but one time its value is 0 so we will eliminate it. Jon has value but it appears twice thus we count it as 1. Harry is available only one time so the total count is 2 now. Saw has 0 value, so we will not count it.

The expected output is -> Total Seller count = 2


Solution

  • enter image description here

    Measure = 
    VAR a = COUNTROWS(VALUES('Table'[Seller]))
    VAR b = COUNTROWS( SUMMARIZE( FILTER('Table', 'Table'[Value] = 0), 'Table'[Seller]))
    
    RETURN a-b