Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

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:

 

  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.

1 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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

9 REPLIES 9
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi Greg

 

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

681x143.png

 

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

 

Or get 

 

large.png

Anonymous
Not applicable

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.. 

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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
Not applicable

Ok Greg...

 

Youll  have it tomorrow..thanks once again

Anonymous
Not applicable

Thanks for the reply...

 

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.