cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
harshadsp Post Patron
Post Patron

DAX function for group by.. count

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft
Microsoft

Re: DAX function for group by.. count

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

View solution in original post

4 REPLIES 4
Microsoft
Microsoft

Re: DAX function for group by.. count

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

View solution in original post

harshadsp Post Patron
Post Patron

Re: DAX function for group by.. count

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

 

Thanks!

Harshad

davemcnab
Frequent Visitor

Re: DAX function for group by.. count

Very helpful Angelia

fduran
Frequent Visitor

Re: DAX function for group by.. count

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

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors