Count duplicates in particular category

[Lauraeire_81]

Hi

I want to count the duplicates for specific categories selected in a slicer. The Countries should be displayed in the slicer.

If America is chosen in the slicer, is there any way to create a calculated column of '3' for the Hot dogs value?

For example see the below table:

Countries	Food
America	Hot dogs
America	Corn dogs
America	Bigmacs
Ireland	Stew
Ireland	Cabbage and bacon
America	Hot dogs
America	Hot dogs

Thanks in advance, 

Laura

[rajendraongole1 · Accepted solution]

Hi @Lauraeire_81  - Yes, you can achieve it both calculated column or measure too. suggest to use measure as like below:

Food Count by Country =

CALCULATE(

    COUNTROWS('test'),

    ALLEXCEPT('test', 'test'[Food]),

    'test'[Countries] IN VALUES('test'[Countries])

)

Hope this helps.

[v-nmadadi-msft]

Hi @Lauraeire_81,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-nmadadi-msft · Accepted solution]

Hi @Lauraeire_81,
Thanks for reaching out to the Microsoft fabric community forum.

The measure provided by @rajendraongole1  pretty much helps to do the count you are looking for

Sharing reference articles for the dax function used in the measure for further reading and uploading sample pbix where i tried out the measure for reference
COUNTROWS function (DAX) - DAX | Microsoft Learn

ALLEXCEPT function (DAX) - DAX | Microsoft Learn


If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[rajendraongole1 · Accepted solution]

Hi @Lauraeire_81  - Yes, you can achieve it both calculated column or measure too. suggest to use measure as like below:

Food Count by Country =

CALCULATE(

    COUNTROWS('test'),

    ALLEXCEPT('test', 'test'[Food]),

    'test'[Countries] IN VALUES('test'[Countries])

)

Hope this helps.