Forum Discussion
Count repeating values
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
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.
2 Replies
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.
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.
