topic Re: Waterfall with custom categories and SWITCH in DAX Commands and Tips

Waterfall with custom categories and SWITCH

DagmaraG — Mon, 03 Apr 2023 09:27:58 GMT

Hi!

Thanks for all the help in previous posts. I have one more issue that is bugging me 🙂

I need to create WATERFALL chart but MEASURE in Y axis need to show different measures based on Category.

I have this Measures set:
Measure1
Measure2
Measure3

Also I have this dimTable:
Index Measure Name
1 Measure1
2 Measure2
3 Measure2

I need to create this waterfall who shows:
Measure1Name ---- Measure1
Measure2Name ---- Measure2

Measure3Name ---- Measure3

I have created Measure in SWITCH that says:

Measure_SWITCH =

SWITCH(

selectedvalue(dimTables[Index]),

1, [Measure1],

2, [Measure2],

3, [Measure3]
)

But this Measure_switch gives only value for Index1 😞 I tried to put Measure_Switch in Y axis and MeasureName in Category but it does only show value for Measure1

Could you help me?

Re: Waterfall with custom categories and SWITCH

PiEye — Mon, 03 Apr 2023 11:09:46 GMT

Hi DagmaraG , I was able to get something to work by doing the following:

3 Measures: Measure 1, Measure 2, Measure 3. Note that these are each DAX measure in the main table:

Measure 1 = [Measure] +1

Measure 2 = [Measure] +2

Measure 3 = [Measure] +3

I then created a parameter, in an unlinked table isolate, with the values 1,2,3 and corresponding switch statement. This returns expected results when tested in a straight table

I can then add these values into a waterfall chart, and the results are as expected.

What are you doing differently? Are the tables connected - they should not be connected. Perhaps if this does not work you can share a screenshot of the model?

PS, if you want even more flexibility around waterfall charts, I have a blog on how to create from bar chart: https://www.iheartdemography.com/post/hacking-a-waterfall-chart-in-powerbi

Re: Waterfall with custom categories and SWITCH

DagmaraG — Mon, 03 Apr 2023 11:58:48 GMT

thanks, I have realized I used to much selected switches. It was an issue with my model.

Re: Waterfall with custom categories and SWITCH

PiEye — Mon, 03 Apr 2023 13:52:14 GMT

Glad to help!