Data grouping for graphical use

[Anonymous]

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:

1. =Switch(Parameters!Grouping.Value = "Department", Fields!Department.Value, Parameters!Grouping.Value = "Division", Fields!Division.Value, Parameters!Grouping.Value = "Location", Fields!Location.Value)
   
   and then…
2. =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.

[Greg_Deckler]

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

[Anonymous]

Apologies fo rthe poorly worded question.....but how could i use the switch function in DAX??? happy to learn..

[Greg_Deckler · Accepted solution]

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.

[Anonymous]

Hi Greg

This is what I am trying to do with the data....

I have tried to Turn on responsive under "General" formatting and Turn off "Concatenate labels" under..

Or get 

[Anonymous]

Image in the previous post has some sample data...I was looking  at SQL Syntax and trying to replicate that in Power BI..the table name is Data_ columns are also called division  location  and department.. 

[Greg_Deckler]

What I really need is sample data that can be copied and pasted into an Enter Data query. Doesn't have to be real data or even much of it.

[Anonymous]

Sample Data

ClientName	Division	Location
Wallmart	9	New York
Wallmart	9	London
Wallmart	9	Toronto
Wallmart	10	New York
Wallmart	10	London
Wallmart	10	Florida
Wallmart	10	DC
Wallmart	8	Toronto
Wallmart	8	New York
Wallmart	8	London
Wallmart	7	Florida
Wallmart	7	New York
Wallmart	7	Texas
Wallmart	7	New York
Wallmart	7	Toronto
Wallmart	6	New York
Wallmart	6	California
Wallmart	6	London
Wallmart	Leadership	DC
Wallmart	Leadership	New York
Wallmart	Leadership	Florida
Wallmart	5	New York
Wallmart	5	Texas
Wallmart	5	California
Wallmart	5	Florida
Wallmart	4	California
Wallmart	4	Toronto
Wallmart	3	California
Wallmart	3	Texas

[Anonymous]

Ok Greg...

Youll  have it tomorrow..thanks once again

[Anonymous]

Thanks for the reply...

Let me get some sample data to you. with a better example and explanation. Much Appreicated,