Forum Discussion

DagmaraG's avatar
DagmaraG
Frequent Visitor
3 years ago
Solved

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

 

 

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? 
  • PiEye's avatar
    PiEye
    3 years ago

    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?

     

    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

3 Replies

  • PiEye's avatar
    PiEye
    Icon for Resolver II rankResolver II
    3 years ago

    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?

     

    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

    • DagmaraG's avatar
      DagmaraG
      Frequent Visitor
      3 years ago

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