A Very Strange Performance Issue

[Anonymous]

Hello all,

The measure called Final Measure below works very slowly on visuals we use with "Drill-Down" and "Matrix". In the performance results we make in PowerBI (visuals used with similar size measures), it can reach 25 seconds at some points. 

My Connection type is Live Connection trough Analysis Services

I have shared Final Measure and other important measures that make up it. I need to imrpove the performance results of the querys

Because it has 1.5Million Rows of Data and it gives the memory limit errors.

Final Measure:=IF (SELECTEDVALUE ( Dates[Is Month] ) = TRUE ()
 || [Selected Metric] = "CC",IF (SELECTEDVALUE ( Customer[Cust:Key] ) <> "44/05/AA/2208909",IF (ISFILTERED ( Category[Category Name] ) = TRUE (),
            [Sub Measure 1],
            [Sub Measure 1] 
               - CALCULATE (                    
[Sub Measure 1],                    
Category[Category Name] = "X Group"                
)        
),        
[Similar to Sub Measure 1]    
),    
[Similar to Sub Measure 1])

Sub Measure 1 :=SWITCH (TRUE (),    
[Selected Metric] = "Actual",IF (            
[Search Filtered Business] = TRUE (),            
[Sub Measure 2]                
+ CALCULATE (                    
[Sub Measure 2],                    
Category[Category Name] = "X Group",ALL ( Category )               
 ),           
 [Sub Measure 2]       
 ),    
[Selected Metric] = "CC",IF (            
[Search Filtered Business] = TRUE (),           
 [Similar to Sub Measure 2]               
 + CALCULATE (                   
 [Similar to Sub Measure 2],                   
 Category[Category Name] = "X Group",ALL ( Category )               
 ),
           
 [Similar to Sub Measure 2]       
 ),    
[Selected Metric] = "Volume",IF (           
 [Search Filtered Business] = TRUE (),           
 [Similar to Sub Measure 2]                
+ CALCULATE (                   
 [Similar to Sub Measure 2],                   
 Category[Category Name] = "X Group",ALL ( Category )               
 ),            
[Similar to Sub Measure 2]       
 ),BLANK ())

Sub Measure 2:= SWITCH(TRUE(),    
[Selected Metric] = "YTD",TOTALYTD(SUM(Sales_Summary[Actual]),'Dates'[Date])   
 ,    
[Selected Metric] = "MTD",TOTALMTD(SUM(Sales_Summary[Actual]),'Dates'[Date])   
 ,BLANK())

İts very Critical and it hapenned after I used this 3 daxes. Let me know if you have any questions to solve this

Thank you all for your time.

[Anonymous]

@Anonymous 
There are no exact solutions for optimization, you might want to follow the techniques to improve performance.

Improve Power BI Performance by Optimizing DAX | MAQ Software

Paul Zheng _ Community Support Team

[Greg_Deckler]

@Anonymous - DAX Performance optimization is a deep subject. Would need to have a better sense of your data and how the measures are being used. If you can share PBIX could take a closer look. If you can't do that, here is a series I wrote on performance optimization in DAX:

• Performance Tuning DAX - Part 1 - Microsoft Power BI Community
• Performance Tuning DAX - Part 2 - Microsoft Power BI Community