Forum Discussion

DenbyG's avatar
DenbyG
Frequent Visitor
9 months ago
Solved

Distinct Count

Hi, I would like to do a distinct count of all the items in a coloumn 'LAR' that is ZPROG001, and all the items in another column, 'AES' that is 10 or 11, in Power Bi. I've tried several different DAX's but I can't seem to get it to work. I'm learning DAX so would appreciate some support.

  • Brilliant. Thank you. I appreciate that. It is CountRows.

4 Replies

  • You can do this with a simple CALCULATE+ DISTINCTCOUNT.

    If you want the distinct count of rows where LAR = "ZPROG001" and AES = 10 or 11, try:

    meas_Count_LAR_AES =
    CALCULATE(
    DISTINCTCOUNT(YourTable[YourRowID]),
    YourTable[LAR] = "ZPROG001",
    YourTable[AES] IN { 10, 11 }
    )

     

    Replace YourTable with your table name and YourRowID with a unique row column.

    If LAR is already unique per row, you can also distinct-count LAR directly:

    meas_Count_LAR_AES =
    CALCULATE(
    DISTINCTCOUNT(YourTable[LAR]),
    YourTable[LAR] = "ZPROG001",
    YourTable[AES] IN { 10, 11 }
    )

     
     

    Shai Karmani | Data & Analytics

    If it helped  please mark as resolved & give a kudo so others can find it too.

    Let’s connect on LinkedIn

    • DenbyG's avatar
      DenbyG
      Frequent Visitor

      Hi, Sorry but it just returns a value of 1. I know there are more than that

      Learning aim reference
      ZPROG001
      Z0059747
      ZPROG001
      Z0059747
      ZPROG001
      ZPROG001
      ZPROG001
      ZPROG001
      ZPROG001
      ZPROG001
      Z0059747
      ZPROG001
      ZPROG001
      Z0059747
      ZPROG001
      Applicable employment status
      11
      0
      11
      0
      2
      10
      10
      10
      11
      10
      0
      11
      10
      0
      1
       
         
         
         
         
         
         
         
         
      • MasonMA's avatar
        MasonMA
        Super User

        Would you want to count unique items or all items? If it is a distinct count, the Measure should be all good. If you would want to count all rows that meet the conditions, then change the DISTINCTCOUNT() to COUNTROWS('table').