Forum Discussion
Data grouping for graphical use
Hi Experts
How would i you convert the following SQL sytnax
The data that produces the bar chart is then grouped by two category groups:
- =Switch(Parameters!Grouping.Value = "Department", Fields!Department.Value, Parameters!Grouping.Value = "Division", Fields!Division.Value, Parameters!Grouping.Value = "Location", Fields!Location.Value)
and then… - =Switch(Parameters!Grouping2.Value = "Department", Fields!Department.Value, Parameters!Grouping2.Value = "Division", Fields!Division.Value, Parameters!Grouping2.Value = "Location", Fields!Location.Value)
, where:
- Parameters!Grouping.Value is the parameter chosen for Grouping 1
- Parameters!Grouping2.Value is the parameter chosen for Grouping 2
So that the equilvant can be done in Power BI.
Most people use this:
https://msdn.microsoft.com/en-us/query-bi/dax/dax-function-reference
For the first one it would be something like:
Measure = SWITCH( MAX([Parameters!Grouping.Value]), "Department",MAX([Fields!Department.Value]), "Division",MAX([Fields!Division.Value]), "Location",MAX([Fields!Location.Value]) )But, incredibly difficult to say without sample/example data. And I'm not even sure you want to be using DAX honestly, you might want to use M (Power Query). Just not nearly enough information to know.
9 Replies
Seems like you would use the SWITCH statement in DAX for that but tough to say exactly. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Most people use this:
https://msdn.microsoft.com/en-us/query-bi/dax/dax-function-reference
For the first one it would be something like:
Measure = SWITCH( MAX([Parameters!Grouping.Value]), "Department",MAX([Fields!Department.Value]), "Division",MAX([Fields!Division.Value]), "Location",MAX([Fields!Location.Value]) )But, incredibly difficult to say without sample/example data. And I'm not even sure you want to be using DAX honestly, you might want to use M (Power Query). Just not nearly enough information to know.