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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
DagmaraG
Frequent Visitor

Waterfall with custom categories and SWITCH

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

 

DagmaraG_0-1680513863548.png

 

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? 
1 ACCEPTED SOLUTION
PiEye
Resolver II
Resolver II

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

PiEye_0-1680519962850.pngPiEye_1-1680520042523.png

 

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

PiEye_2-1680520136739.png

 

 

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?

 

Pi

 

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

View solution in original post

3 REPLIES 3
PiEye
Resolver II
Resolver II

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

PiEye_0-1680519962850.pngPiEye_1-1680520042523.png

 

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

PiEye_2-1680520136739.png

 

 

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?

 

Pi

 

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

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

Glad to help!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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