Count based on distinct other column

[Carliniiiii]

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. 

[Carliniiiii]

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

[qqqqqwwwweeerrr]

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

[Carliniiiii]

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

[qqqqqwwwweeerrr]

Hi @Carliniiiii 

You can use this measure 

UniqueCategoryACount =

CALCULATE(

    DISTINCTCOUNT('Table'[Identifier ]),

    'Table'[Category] = "A"

)

let me know if doesnot works

Regards

[qqqqqwwwweeerrr · Accepted solution]

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