DAX Switch Formula Failing to Load in Waterfall Chart

[PaulPannu]

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.

[v-yetao1-msft]

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.