HiI have the following table:Food Category Food AmericanHot DogsIrish Hot DogsItalianPastaAmericanFriesIrish Ham and CabbageIrish Shepherds pieAmericanCorn Dogs I would like to create three measures for the following:Count: 2 are only American (Fries, Corn Dogs) - non-repeated values in Food ColumnCount: 1 are American and Irish (Hot Dogs) - repeated values in Food ColumnCount: 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

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

Count values in categories | Microsoft Fabric Community

10 Replies

bhanu_gautam
Super User
1 year ago
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
  Helper I
  1 year ago
  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
    Resolver II
    1 year ago
    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
    )

danextian

Super User

1 year ago

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

Khushidesai0109
Skilled Sharer
1 year ago
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)
Lauraeire_81
Helper I
1 year ago
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
  Super User
  1 year ago
  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
    Helper I
    1 year ago
    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.
sroy_16
Resolver II
1 year ago
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.
v-kathullac
Community Support
1 year ago

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

Forum Discussion

Count values in categories

10 Replies

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

Related content

Counting nulls

Count values that are in two categories

Count rows per category

Count by Category

Count of values not accurate

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