cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PaulPannu
Frequent Visitor

DAX Switch Formula Failing to Load in Waterfall Chart

Hello All, 

Looking for assistance on a DAX formula that is seriously limiting my ability to build a report. The report centres on a Waterfall chart that is fed by a SWITCH formula, to allow for multiple measures to show the variances between two datasets (image below).

The measure that feeds the chart is below: 

NS Waterfall Value =
SWITCH (
VALUES ( 'NS Account Table'[Index] ),
1, [Vol/Mix Net Sales]/1000,
2, [Rate xFX Gross Sales]/1000,
3, [Rate xFX Promo Allowance]/1000,
4, [Rate xFX Other Trade]/1000,
5, [Rate xFX Slotting]/1000,
6, [Rate xFX Coupons]/1000,
7, [Rate xFX Cash Disc. and Unsaleable]/1000,
BLANK())

The above is set up in a manner, in that an unconnected table is used as the category option for the waterfall chart, and then used in a switch formula to then sub in the individual measures into the points of the waterfall chart below. All measures are set up in the same way, with an example below: 

 
Calculate('Combined P&L'[Rate xFX],Account[Sub-Account]="Customer Sales")+Calculate('Combined P&L'[Rate xFX],Account[Sub-Account]="CPU Allowance")

The Rate xFX measure being used, is another measure however that runs in 700-1000 ms, thus I have assumed this is not the issue. Stand alone with no filters, the above switch function can run up to 15 seconds, and when filtering for different characteristics, that can run up to a full minute, or even crash with the message stating "Not enough memory to complete this operation". 
 
Any help would be much appreciated, and feel free to let me know if any other data is needed.

 

 

waterfall chart.PNG

1 REPLY 1
v-yetao1-msft
Community Support
Community Support

Hi @PaulPannu 

Try increasing the Maximum allowed to 16000 under File / Options and Settings / Options / Global / Data Load.  Data Cache Management Options.

You can also refer to the link below to see if it helps you .

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=6cd37af6-... 

 

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors