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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!:
Mastering Power BI 2nd Edition

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!:
Mastering Power BI 2nd Edition

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!:
Mastering Power BI 2nd Edition

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!:
Mastering Power BI 2nd Edition

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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