Forum Discussion

Lauraeire_81's avatar
Lauraeire_81
Helper I
1 year ago
Solved

Count values in categories

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 America...
Need Help
Show and Tell
Tips and Tricks
  • v-kathullac's avatar
    v-kathullac
    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

Lauraeire_81's avatar
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's avatar
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's avatar
    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.