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

[v-kathullac · Accepted solution]

Hi @Lauraeire_81 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?

If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,

Chaithanya

[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.

[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" )
)

[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
)
)
)

[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

)

[sroy_16]

HI @Lauraeire_81 

Please give this a try and let me know if it meets your requirements.

IrishandAmerican =
VAR selectedValues = VALUES('Sheet1'[Column1])
RETURN
IF (
COUNTROWS(selectedValues) = 2
&& "Irish" IN selectedValues
&& "American" IN selectedValues,
CALCULATE (
DISTINCTCOUNT('Sheet1'[Column2]),
'Sheet1'[Column1] IN {"Irish", "American"}
),
0
)