Count repeating values

[Lauraeire_81]

Hi all,

I am looking to count the number of duplicates in a calculated column. 

The scenario is as follows:

Country  Food
American - Hotdogs
American - Hotdogs
American - Fries
American - Fries
American - Fries
American - Burgers
Irish - Hotdogs
Irish - Fries
Irish - Stew
Italian - Pizza
Italian - Hotdogs
Italian - Hotdogs
Italian - Hotdogs

The Slicer contains 'American', 'Irish' and 'Italian'.
If I select 'American' on the Slicer, I want to add a calculated column to show only 3 for Hotdogs and not display the other repeating values of 'Hotdogs'.

The same would be to just show 3 beside Fries and only display the one count of 3 for Fries.
--------------------------------------------------------------------------------------

If both 'American' and 'Italian' are selected it should only have three columns of below
Food        Count
Hotdogs  5 (American has 2 and Italian has 3)
Pizza        1 (Count this even though there's only one value)
Burgers    1 (Count this even though there's only one value)
Fries         3 ( American has 3 values of Fries)

I hope this makes sense. I want to remove any repeating values that I don't need.

Thanks in advance for your help.

Regards,

Laura

Thanks

Laura

[anmolmalviya05 · Accepted solution]

Hi @Lauraeire_81,

This is a classic case where you want a summarized count of food items filtered by slicer selections, but only showing one row per food item with the total count. This should not be done using a calculated column. Instead, you should use a measure and build a visual table that responds to slicers.

Food Count =

CALCULATE(

COUNTROWS(FoodTable),

ALLEXCEPT(FoodTable, FoodTable[Food])

)

Add a table visual:

• Put Food in the rows.
• Add the Food Count measure in the values.
• Add Country to the slicer.

If this post helps to answer your question, please consider accepting it as a solution so others can find it more quickly when they face a similar challenge.

Proud to be a Microsoft Fabric community super user

Let's Connect on LinkedIn

Subscribe to my YouTube channel for Microsoft Fabric and Power BI updates.

[Akash_Varuna]

Hi @Lauraeire_81  This could be achieved by creating a DAX measure like: 

Food Count = 
CALCULATE(
    COUNT('FoodData'[Food]),
    ALLEXCEPT('FoodData', 'FoodData'[Food])
)

Add this measure to a table visual alongside the Food column. The slicer will dynamically filter the selected countries, and the measure will display the unique counts for each food item.