count without duplicates

[kheira]

hi 
i have two columns and i want to count how many product id in statmeent without duplicates production with the same id 
how i can do that with dax? 

[Anonymous · Accepted solution]

Hello @kheira 
Count I want =

VAR StatementCount =

CALCULATE(

DISTINCTCOUNT('Table'[Satement ID])

)

VAR ProductCount =

CALCULATE(

COUNT('Table'[Product ID]),

ALL('Table'[Product ID])

)

RETURN

ProductCount - StatementCount

[kheira]

thank you 😍

[AlB]

@kheira 

I guess you mean a 3 for the statement id 6

New column =
CALCULATE (
    DISTINCTCOUNT ( Table1[Product ID] ),
    ALLEXCEPT ( Table1, Table1[statement id] )
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @kheira 

1. Place Statement ID in a table visual

2. Place this measure in the visual

Measure =
DISTINCTCOUNT( Table1[Product ID])

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[kheira]

thank u 
but i want to add new coulmn dax to get the result .. i tried to do dax :

CALCULATE( COUNT('Table'[product id]), ALLEXCEPT('Table','Table'[product id])) - CALCULATE( DISTINCTCOUNT('Table'[statment id]), ALLEXCEPT('Table','Table'[statment id]))

but i want  the count  is 4 in "statment id "=6