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

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?

View solution in original post

9 REPLIES 9
Anonymous
Not applicable

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?

Anonymous
Not applicable

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

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

Anonymous
Not applicable

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

Hi @Anonymous,

 

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.

Anonymous
Not applicable

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.

 

Hi @Anonymous,

 

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

Anonymous
Not applicable

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.

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.