cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ovetteabejuela Established Member
Established Member

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 Super Contributor
Super Contributor

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 Super Contributor
Super Contributor

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 Super Contributor
Super Contributor

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 Established Member
Established Member

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 Super Contributor
Super Contributor

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 Established Member
Established Member

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 Super Contributor
Super Contributor

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

Ross73312 Super Contributor
Super Contributor

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
Frequent Visitor

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

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors