Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 @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
Thank you Angelina! This is exactly what I needed to summarize my data in a stats table!
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
Very helpful Angelia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |