topic Re: count without duplicates in DAX Commands and Tips

count without duplicates

kheira — Wed, 13 Jan 2021 09:06:11 GMT

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?

Re: count without duplicates

AlB — Wed, 13 Jan 2021 09:24:00 GMT

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

Re: count without duplicates

kheira — Wed, 13 Jan 2021 09:49:41 GMT

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

Re: count without duplicates

AlB — Wed, 13 Jan 2021 11:39:27 GMT

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

Re: count without duplicates

Anonymous — Wed, 13 Jan 2021 11:51:47 GMT

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

Re: count without duplicates

kheira — Wed, 13 Jan 2021 12:50:23 GMT

thank you 😍