cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ovetteabejuela Impactful Individual
Impactful Individual

DAX: Switching Formula according to Column(Category)

Hi Community,

 

Would like some help with switch formula in DAX:

 

I'd like to have something like this:

Result = Switch([Category Column],
                          "ABC", [ColumnA] + [ColumnB] + [ColumnC],
                          "A,E", [ColumnA]+[ColumnE],
                          "BEG", [ColumnB] + [ColumnE] + [ColumnG]
                          )

If that is not possible with switch, what would you suggest?

1 ACCEPTED SOLUTION

Accepted Solutions
Ross73312 Community Champion
Community Champion

Re: DAX: Switching Formula according to Column(Category)

This is one i literally use in one of my projects and works great:

Graph Result = SWITCH([Graph Selection],
    2, [Samples Per FTE],
    3, 0,
    4, 0,
    5, [EBIT per FTE],
    6, [TEC Per FTE],
    7, [TEC as Percent Revenue],
    [Revenue per FTE])

 

In mine, i use it as a method of having someone select from a drop down which graph that want to display.  They pick from a list and then the graph changes on screen.

I don't see a reason why your code won't work. Have you had a specific issue with it?


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

9 REPLIES 9
Ross73312 Community Champion
Community Champion

Re: DAX: Switching Formula according to Column(Category)

This is one i literally use in one of my projects and works great:

Graph Result = SWITCH([Graph Selection],
    2, [Samples Per FTE],
    3, 0,
    4, 0,
    5, [EBIT per FTE],
    6, [TEC Per FTE],
    7, [TEC as Percent Revenue],
    [Revenue per FTE])

 

In mine, i use it as a method of having someone select from a drop down which graph that want to display.  They pick from a list and then the graph changes on screen.

I don't see a reason why your code won't work. Have you had a specific issue with it?


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


View solution in original post

Ross73312 Community Champion
Community Champion

Re: DAX: Switching Formula according to Column(Category)

I do notice you don't have an "Else" though.  Perhaps you need that.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


ovetteabejuela Impactful Individual
Impactful Individual

Re: DAX: Switching Formula according to Column(Category)

Okay, I was trying to push this into a New Measure not with a New Column. Used New Column and works fine now. I was trying to avoid New Column because I am not interacting with the results.

 

Thanks @Ross73312

Ross73312 Community Champion
Community Champion

Re: DAX: Switching Formula according to Column(Category)

Hi @ovetteabejuela my solution does use a measure and not a column.  I think i see the issue you are having. You need values from each of those columns added together which is more what you are struggling with.  The switch isn't really the issue here.  What you need is a measure for each of those possbilities then you need to sum those measures together. 

For example, the first measure might be for Attribute "AAA".  So you filter inside the measure so you are only considering records with that attribute.
Then if "AAA" is "Col 1" + "Col 2" + "Col 3" you can simply go sum("Col1") + sum("Col2") + sum("Col3").

Repeat this for each type. 1 measure each.  Have a summary measure with is Measure1 + Measure 2 + .... + MeasureX


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


ovetteabejuela Impactful Individual
Impactful Individual

Re: DAX: Switching Formula according to Column(Category)

Hi @Ross73312,

 

this line SWITCH([Graph Selection]

 

is Graph Selection a column in your table or a Measure? Asking because I'm not showing any Column as a selection in this parameter.

 

I am pulling categories from a column and calculate based on that category.

Ross73312 Community Champion
Community Champion

Re: DAX: Switching Formula according to Column(Category)

In my example [Graph Selection] is a measure which translates to:

Graph Selection = min('Graph Selection'[ID])


The way this works is I have a slicer on my report.  A user selects the graph they want from the slicer, which looks like a dropdown menu.  That effectively filters that Graph Selection table so it only has a single row, that row being the selected graph.  The Graph Selection measure simply returns a single value, and allows the Graph Result measure to point to the measure to display on the Graph Tile.

This means i point my Graph Tile at a single measure, then use the switch statement to point off to a set of different measures.  This allows a user driven output.

 


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Hspuybroek
Frequent Visitor

Re: DAX: Switching Formula according to Column(Category)

Hi @Ross73312,

 

Could you share your example with the graph selection? With your example is it possible to choose for example the dimension Product or Customer? And what the users selects the same graph will adjust?

 

To be clear; i made it already possible with the switching formula to change the graph to count or amount. But I want to change the  dimension also. So now the graph is product amount or product count. I want the possibility that the user can choose an other dimension like Customer. And that the same graph or table will change then. 

 

Thank you!

 

Regards, Hilbert

Highlighted
Ross73312 Community Champion
Community Champion

Re: DAX: Switching Formula according to Column(Category)

In my graph example the only set dimensions is Date, which comes from a Date Dimension table that is linked to all other tables.  It should also be noted that my slicers are based on Sites, which is a table in the middle of my table relationships.  I then have tables that hold Employee Data, Sales Data, Expense data.  In this case all of the data is in someway linked to both Date and Sites.

 

The switching method only works with data that can be held within Measures and Slicers.  So for your circumstance, consider whether your graph modifications can be held in a measure to be graphed and whether you can correctly restrict the data using slicers.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


Hspuybroek
Frequent Visitor

Re: DAX: Switching Formula according to Column(Category)

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors