Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Solved! Go to Solution.
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
Thank you Angelina! This is exactly what I needed to summarize my data in a stats table!
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
Very helpful Angelia
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
86 | |
76 | |
74 | |
56 | |
45 |
User | Count |
---|---|
117 | |
105 | |
77 | |
66 | |
64 |