topic Re: Count based on distinct other column in DAX Commands and Tips

Count based on distinct other column

Carliniiiii — Wed, 05 Jun 2024 12:40:01 GMT

Hello Power BI community,

I want to count the categories in a table per distinct identifier once. I can't remove the duplicates as they contain different data in other columns.

Identifier	Category
Title 1	A
Title 1	A
Title 1	A
Title 2	B
Title 2	B
Title 3	A
Title 3	A
Title 4	C

Desired Output

A	2
B	1
C	1

Ideally I can utilize the output directly in a pie chart to show the distribution of the categories whether be it in a measure or a separate column.

Thank you all for your help.

Re: Count based on distinct other column

qqqqqwwwweeerrr — Wed, 05 Jun 2024 13:14:50 GMT

Hi Carliniiiii

If my understanding is correct you want distinct count of identifier for a given category?

it can be direclty also in power in pie chart check below image

is identifier in value and take distinct count or you can create measure:

UniqueCategoryCount =

CALCULATE(

DISTINCTCOUNT('Table'[Identifier ]),

ALLEXCEPT('Table', 'Table'[Category])

)

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

Regards

Re: Count based on distinct other column

Carliniiiii — Wed, 05 Jun 2024 13:28:24 GMT

Hi qqqqqwwwweeerrr,

thank you for your help. Is there also a way to extract the category amount and represent it in a card? So for instance Title category = A, Amount 2

Thanks

Re: Count based on distinct other column

qqqqqwwwweeerrr — Wed, 05 Jun 2024 13:43:40 GMT

Hi Carliniiiii

If that answer helps you please mark that as an accpeted solution so other might use if they have similar problem.

Seocnd: when you amount? is the same as count?or what like if category is B then what is expectation other than this do you want to only show A or B reason being Card will only take first value or total value or spefic manually given say only want to show value for A. I might have confused you but can please elobrate your question in detail with expected output and if any condition also want to included.

Regards

Re: Count based on distinct other column

Carliniiiii — Wed, 05 Jun 2024 14:40:35 GMT

Hi qqqqqwwwweeerrr

"spefic manually given say only want to show value for A"

I think this is the most accurate answer. Basically I want to create a card which represents the counted amount of category A based on the measure above. So the card says as callout value "2"

Thanks

Re: Count based on distinct other column

qqqqqwwwweeerrr — Wed, 05 Jun 2024 17:24:44 GMT

Hi Carliniiiii

You can use this measure

UniqueCategoryACount =

CALCULATE(

DISTINCTCOUNT('Table'[Identifier ]),

'Table'[Category] = "A"

)

let me know if doesnot works

Regards