Forum Discussion

Excellove15's avatar
Excellove15
Iron Contributor
Sep 17, 2024

Dax referenced measures causes performance issues

Hi,

 

I have created a dax measure as below:

 

VT_Targetunitrate_2 =
SUMX ( Accruals, IF ( [Accrual cost source_2] = "Target", [Switch Cost] ) )

 

I also tried using calculate but i am not able to apply the filter condition because [Accrual cost source_2] is a dax measure

 

FYI, Accrual Cost Source_2 & Switch cost in above dax code  are both separate measures as below:

 

 

[Priority 1],[Priority 2] , [Priority 3] & Parameter Value cost are in turn referenced measures.

 

But this takes a lot of time to run in visual and its causing performance issues.

 

PFA file here https://1drv.ms/u/s!Au2HPfdnp63Pf9G8csY_rqTDEYA?e=CBhhcT

Can you please help how to improve performance as this is only a sample file with few million rows. 

 

Please let me know if you need further info!

 

Thanks in advance!

SergeiBaklan 

BI & Data Analysis
copilot in excel
excel
Formulas and Functions
Macros and VBA
office 365
power bi

1 Reply

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Sep 20, 2024

    Hi Excellove15 

     

    Can you please help how to improve performance Not with my DAX knowledge. Though, you seem to SWITCH here and there so, just in case:

    • https://blog.crossjoin.co.uk/2023/11/26/if-switch-and-the-effect-of-dax-variables-on-strict-eager-evaluation/n
    • https://blog.crossjoin.co.uk/2022/09/19/diagnosing-switch-related-performance-problems-in-power-bi-dax-using-evaluateandlog/

    Also, and no idea if DAX behaves the same but with Excel when you do something like:

    =SWITCH( TRUE,
  A1 = 1, 1,
  A1 = 2, 2,
  A1 = 3, 3,
  "Custom"
)

    ALL conditions are evaluated

Resources