Count values in categories

Lauraeire_81

Hi

I have the following table:

Food Category	Food
American	Hot Dogs
Irish	Hot Dogs
Italian	Pasta
American	Fries
Irish	Ham and Cabbage
Irish	Shepherds pie
American	Corn Dogs

I would like to create three measures for the following:

Count: 2 are only American (Fries, Corn Dogs) - non-repeated values in Food Column

Count: 1 are American and Irish (Hot Dogs) - repeated values in Food Column

Count: 1 are Italian (Pasta) etc..

I am creating a Venn Diagram so will need to get these counts for each area of it.

Thanks in advance.

Regards,

Laura

Khushidesai0109

Hiii @Lauraeire_81

Measure 1: Count of Foods that are only American
Only_Ameican_Count =
VAR AmericanFoods =
FILTER(
VALUES('Table'[Food]),
CALCULATE(DISTINCTCOUNT('Table'[Food Category]), ALL('Table')) = 1
&& MAX('Table'[Food Category]) = "American"
)
RETURN
COUNTROWS(AmericanFoods)

Measure 2: Count of Foods that are American & Irish
American_And_Irish_Count =
VAR AmericanIrishFoods =
FILTER(
VALUES('Table'[Food]),
CALCULATE(DISTINCTCOUNT('Table'[Food Category]), ALL('Table')) = 2
&& "American" IN VALUES('Table'[Food Category])
&& "Irish" IN VALUES('Table'[Food Category])
)
RETURN
COUNTROWS(AmericanIrishFoods)

Measure 3: Count of Foods that belong to only one category (e.g., Italian, Irish, etc.)
Single_Category_Count =
VAR SingleCategoryFoods =
FILTER(
VALUES('Table'[Food]),
CALCULATE(DISTINCTCOUNT('Table'[Food Category]), ALL('Table')) = 1
)
RETURN
COUNTROWS(SingleCategoryFoods)

sroy_16

Hello @Lauraeire_81

Please try using these measures and see if it fulfils your requirement.

Count_American_Only =
CALCULATE (
DISTINCTCOUNT (FoodCategory[Food]),
FoodCategory[Food Category] = "American",
NOT (
FoodCategory[Food] IN
CALCULATETABLE (
VALUES (FoodCategory[Food]),
FoodCategory[Food Category] <> "American"
)
)
)

Count_American_and_Irish =
CALCULATE (
DISTINCTCOUNT (FoodCategory[Food]),
FoodCategory[Food] IN
CALCULATETABLE (
VALUES (FoodCategory[Food]),
FoodCategory[Food Category] = "American"
),
FoodCategory[Food] IN
CALCULATETABLE (
VALUES (FoodCategory[Food]),
FoodCategory[Food Category] = "Irish"
)
)

Count_Italian_Only =
CALCULATE (
DISTINCTCOUNT (FoodCategory[Food]),
FoodCategory[Food Category] = "Italian",
NOT (
FoodCategory[Food] IN
CALCULATETABLE (
VALUES (FoodCategory[Food]),
FoodCategory[Food Category] <> "Italian"
)
)
)

I hope the solution I provided helps! If it works for you and solves your issue, please feel free to mark it as a solution so it can assist others who may have the same problem.

Lauraeire_81

HI @bhanu_gautam Thank you this helps.

What does EARLIER mean? Also if I had a slicer with 3 values (American, Irish, Italian), can you show me how this works with those measures above?

I'd like to leave all values as '0' if there are no slicer values selected.

Thanks for your help.

Regards,

Laura

bhanu_gautam

@Lauraeire_81 The EARLIER function in DAX is used to refer to an earlier row context in a nested row context

To incorporate a slicer with values (American, Irish, Italian) and ensure that the measures return '0' if no slicer values are selected, you can modify the measures to include a check for the slicer selection. Here are the updated measures:

dax
CountOnlyAmerican =
IF(
ISFILTERED('Table'[Category]),
CALCULATE(
COUNTROWS(
FILTER(
VALUES('Table'[Food]),
COUNTROWS(
FILTER('Table', 'Table'[Food] = EARLIER('Table'[Food]) && 'Table'[Category] = "American")
) = 1 &&
COUNTROWS(
FILTER('Table', 'Table'[Food] = EARLIER('Table'[Food]) && 'Table'[Category] <> "American")
) = 0
)
)
),
0
)

dax
CountAmericanAndIrish =
IF(
ISFILTERED('Table'[Category]),
CALCULATE(
COUNTROWS(
FILTER(
VALUES('Table'[Food]),
COUNTROWS(
FILTER('Table', 'Table'[Food] = EARLIER('Table'[Food]) && 'Table'[Category] = "American")
) > 0 &&
COUNTROWS(
FILTER('Table', 'Table'[Food] = EARLIER('Table'[Food]) && 'Table'[Category] = "Irish")
) > 0
)
)
),
0
)

dax
CountItalian =
IF(
ISFILTERED('Table'[Category]),
CALCULATE(
COUNTROWS(
FILTER(
VALUES('Table'[Food]),
COUNTROWS(
FILTER('Table', 'Table'[Food] = EARLIER('Table'[Food]) && 'Table'[Category] = "Italian")
) > 0
)
)
),
0
)

These measures use the ISFILTERED function to check if the slicer is applied to the 'Category' column. If the slicer is not applied, the measures return '0'. If the slicer is applied, the measures perform the calculations as described.

Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Lauraeire_81

Thanks @bhanu_gautam it gives me back a count of '3' when American is selected. Can you please send me the powerbi pbx file ? Thanks for your help.

danextian

Hi @Lauraeire_81

Please try the following:

Measure01 = 
CALCULATE (
    DISTINCTCOUNT ( _data[Food Category] ),
    KEEPFILTERS ( _data[Food] IN {"Hot Dogs", "Fries"} )
)


Measure02 = 
CALCULATE (
    DISTINCTCOUNT ( _data[Food Category] ),
    KEEPFILTERS ( _data[Food] =  "Hot Dogs" )
)

Measure03 = 
CALCULATE (
    DISTINCTCOUNT ( _data[Food Category] ),
    KEEPFILTERS ( _data[Food] = "Pasta" )
)

Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

bhanu_gautam

@Lauraeire_81 Create measure

Count of Foods that are only American (non-repeated values in Food Column):

DAX
CountOnlyAmerican =
CALCULATE(
COUNTROWS(
FILTER(
VALUES('Table'[Food]),
COUNTROWS(
FILTER('Table', 'Table'[Food] = EARLIER('Table'[Food]) && 'Table'[Category] = "American")
) = 1 &&
COUNTROWS(
FILTER('Table', 'Table'[Food] = EARLIER('Table'[Food]) && 'Table'[Category] <> "American")
) = 0
)
)
)

Count of Foods that are American and Irish (repeated values in Food Column):

DAX
CountAmericanAndIrish =
CALCULATE(
COUNTROWS(
FILTER(
VALUES('Table'[Food]),
COUNTROWS(
FILTER('Table', 'Table'[Food] = EARLIER('Table'[Food]) && 'Table'[Category] = "American")
) > 0 &&
COUNTROWS(
FILTER('Table', 'Table'[Food] = EARLIER('Table'[Food]) && 'Table'[Category] = "Irish")
) > 0
)
)
)

Count of Foods that are Italian:

DAX
CountItalian =
CALCULATE(
COUNTROWS(
FILTER(
VALUES('Table'[Food]),
COUNTROWS(
FILTER('Table', 'Table'[Food] = EARLIER('Table'[Food]) && 'Table'[Category] = "Italian")
) > 0
)
)
)

Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!

Lauraeire_81

hI @bhanu_gautam @sroy_16 @danextian - Thanks for all of your help.

Can you please help with the following DAX:

I want to Count the values of food in Irish and American if 2 values are selected in the slicer (America and Irish), can you please help with this?

IrishandAmerican =

var selected = SELECTEDVALUE('Sheet1'[Column1])

return

if (selected = "Irish""American",

CALCULATE (

DISTINCTCOUNT (Sheet1[Column2]),

Sheet1[Column1] = "American",

NOT (

(Sheet1[Column2] IN

CALCULATETABLE (

VALUES (Sheet1[Column2]),

(Sheet1[Column1] <> "Irish")

)

))),

0

)

Count values in categories