Switch syntax causing issues with loading visuals

Question

Hi,&nbsp;Please could you help me to simply the below DAX. Our Dataset is connected to SAP HANA via direct query. The visual is not getting loaded as the query is consuming a lot of profile memory allocated in HANA. On the analysis, it was found that this switch syntax is causing the issue.&nbsp;&nbsp;Suppress Sales Switch =SWITCH(SELECTEDVALUE('Total Sales Slicer'[Total Sales Criteria]) ,"Total Sales = 0", if([Suppress Sales] = 0, [Suppress Sales], BLANK()),"Total Sales &lt; 0", if([Suppress Sales] &lt; 0, [Suppress Sales], BLANK()),"Total Sales &gt; 0", if([Suppress Sales)] &gt; 0, [Suppress Sales], BLANK()),"Total Sales &lt;&gt; 0", if([Suppress Sales] &lt;&gt; 0, [Suppress Sales)], BLANK()),[Suppress Sales])&nbsp;The above is used as a visual filter and the condition is set to "is not blank". The field&nbsp;Total Sales Criteria is then used in a slicer as the business wants to filter the data by total sales =0, &gt;0,&lt;0,&lt;&gt;0 and All&nbsp;Suppress Sales = sumx('Calculated Measures',abs(COALESCE([Value Field 1],0)) + abs(COALESCE([Value Field 2],0))+ abs(COALESCE([Value Field 3],0)))&nbsp;Total Sales Slicer Table:Total Sales CriteriaFlagTotal Sales All0Total Sales &lt;01Total Sales = 02Total Sales &gt;03Total Sales &lt;&gt;04&nbsp;Any advise to replace Switch which is causing the issue?

mahoneypat · Accepted Answer

That measure (and the one it references) might need more optimization but for starters, you can use a variable like this to reduce to amount of re-calculation.
&nbsp;
Suppress&nbsp;Sales&nbsp;Switch&nbsp;=VAR&nbsp;Suppress&nbsp;=&nbsp;[Suppress&nbsp;Sales]RETURN&nbsp;&nbsp;&nbsp;&nbsp;SWITCH&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SELECTEDVALUE&nbsp;(&nbsp;'Total&nbsp;Sales&nbsp;Slicer'[Total&nbsp;Sales&nbsp;Criteria]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"Total&nbsp;Sales&nbsp;=&nbsp;0",&nbsp;IF&nbsp;(&nbsp;Suppress&nbsp;=&nbsp;0,&nbsp;Suppress,&nbsp;BLANK&nbsp;()&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"Total&nbsp;Sales&nbsp;&lt;&nbsp;0",&nbsp;IF&nbsp;(&nbsp;Suppress&nbsp;&lt;&nbsp;0,&nbsp;Suppress,&nbsp;BLANK&nbsp;()&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"Total&nbsp;Sales&nbsp;&gt;&nbsp;0",&nbsp;IF&nbsp;(&nbsp;Suppress&nbsp;&gt;&nbsp;0,&nbsp;Suppress,&nbsp;BLANK&nbsp;()&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"Total&nbsp;Sales&nbsp;&lt;&gt;&nbsp;0",&nbsp;IF&nbsp;(&nbsp;Suppress&nbsp;&lt;&gt;&nbsp;0,&nbsp;Suppress,&nbsp;BLANK&nbsp;()&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Suppress&nbsp;&nbsp;&nbsp;&nbsp;)
&nbsp;
Pat
&nbsp;

Forum Discussion

Switch syntax causing issues with loading visuals

2 Replies

Related content

Pause visuals from loading

Error causing pipeline failure

Using List.Contains causing long load time

Error fetching data for this visual caused by date slicer

Load error caused by colon

Total Sales Criteria	Flag
Total Sales All	0
Total Sales <0	1
Total Sales = 0	2
Total Sales >0	3
Total Sales <>0	4