Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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!

Show and Tell
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi Anonymous,

    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

4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

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

       

      Thanks!

      Harshad

  • fduran's avatar
    fduran
    Advocate I
    7 years ago

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