DAX function for group by.. count

[harshadsp]

Hello, Need your help!

I am writing the following DAX

Measure = SUMMARIZE(CrimeDataAnalysisParsed,CrimeDataAnalysisParsed[State],CrimeDataAnalysisParsed[LocalDescription],"MostCrimes",count(CrimeDataAnalysisParsed[ID]))

but it says "the expression refers to multiple columns"

Basically I need to write dax for following sql query

select case BeatName
       when 'Beat 1' then 'Texas'
    when 'Beat 2' then 'Illinois'
    when 'Beat 3' then 'Kansas'
    when 'Beat 4' then 'Colorado'
    when 'Beat 5' then 'California'
    when 'Beat 6' then 'Minnesota'
    when 'Beat 7' then 'Ohio'
    when 'Beat 8' then 'Utah'
    when 'Beat 9' then 'Wisconsin'
    else BeatName end state,
  LocalDescription, (count(id)) cnt
from   CrimeDataAnalysisParsed
where  BeatName is not null
group  by BeatName,LocalDescription

I want to get a count of ID by grouping state and localdescriptio.

Please advise.

Thanks!

[fduran]

Thank you Angelina! This is exactly what I needed to summarize my data in a stats table!

[v-huizhn-msft · Accepted solution]

Hi @harshadsp,

SUMMARIZE returns a table rather than value, if you want to use SUMMARIZE fucntion. You need to click "New Table"->under Modeling on Home page, then type the following formula.

NewTable =
SUMMARIZE (
 CrimeDataAnalysisParsed,
 CrimeDataAnalysisParsed[State],
 CrimeDataAnalysisParsed[LocalDescription],
 "MostCrimes", COUNT ( CrimeDataAnalysisParsed[ID] )
)

Otherwise, if you want to create a measure, please use the following formula, then create a table visual, select CrimeDataAnalysisParsed[State],CrimeDataAnalysisParsed[LocalDescription] and measure as values level to display the result.

measure =
CALCULATE (
    COUNT ( CrimeDataAnalysisParsed[ID] ),
    ALLEXCEPT (
        CrimeDataAnalysisParsed,
        CrimeDataAnalysisParsed[State],
        CrimeDataAnalysisParsed[LocalDescription]
    )
)

Please feel free to ask if you have any other issues.

Best Regards,
Angelia

[davemcnab]

Very helpful Angelia

[harshadsp]

@v-huizhn-msft thank you so much! This is what I am looking for.

Thanks!

Harshad