Forum Discussion

Icon for Helper I rank

Helper I

5 years ago

Solved

Dax - Count in Column based on a specific field

Hello, How do I perform a count of the Cons group in each specific reporting unit. The output I am trying to achieve is in green:

ryan_mayu
5 years ago
gracie494

pls add all(), and have a try on below DAX.

calculate(distinctcount[consgroup],filter(ALL(table),[reportingunit]=max([reportingunit]))

9 Replies

amitchandak
Super User
5 years ago
gracie494 , Try

a New column

= calculate(distinctcount(Table[ConsGroup]), filter(Table, [ reporting unit] =earlier([ reporting unit])))
- gracie494
  Helper I
  5 years ago
  amitchandak Thanks, though sadly it didnt work
Greg_Deckler
Community Champion
5 years ago
gracie494 Perhaps:

Measure = VAR __RU = MAX('Table'[Reporting Unit]) VAR __CG = MAX('Table'[ConsGroup]) RETURN COUNTROWS(FILTER(ALL('Table'),[Reporting Unit]=__RU && [ConsGroup]=__CG))
- gracie494
  Helper I
  5 years ago
  Greg_Deckler Thanks Greg, sadly it did'nt work. Below is the result
ryan_mayu
Super User
5 years ago
gracie494

maybe try

calculate(distinctcount[consgroup],filter(table,[reportingunit]=max([reportingunit]))
- gracie494
  Helper I
  5 years ago
  ryan_mayu sadly it didnt work, just returns a 1
  - ryan_mayu
    Super User
    5 years ago
    gracie494
    
    pls add all(), and have a try on below DAX.
    
    calculate(distinctcount[consgroup],filter(ALL(table),[reportingunit]=max([reportingunit]))