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

bhanu_gautam's avatar
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's avatar
    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's avatar
      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
      )