Dax - Count in Column based on a specific field

[gracie494]

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]

@gracie494 

maybe try

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

[gracie494]

@ryan_mayu sadly it didnt work, just returns a 1

[ryan_mayu · Accepted solution]

@gracie494 

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

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

[gracie494]

@ryan_mayu Thank you so much, it worked. 🙂

[ryan_mayu]

you are welcome

[Greg_Deckler]

@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]

@Greg_Deckler Thanks Greg, sadly it did'nt work. Below is the result

[amitchandak]

@gracie494 , Try

a New column

= calculate(distinctcount(Table[ConsGroup]), filter(Table, [ reporting unit] =earlier([ reporting unit])))

[gracie494]

@amitchandak Thanks, though sadly it didnt work