Dax optimization

DAX DAX Calculations
1

Please help. The measure below takes approximately 150 seconds to load. Can you optimize it?

N (fd) =
VAR maxDN =
CALCULATE (
MAX ( ‘DWH DimDate’[DateName] ),
ALLSELECTED ( ‘DWH DimDate’[DateName] )
)
VAR minDN =
CALCULATE (
MIN ( ‘DWH DimDate’[DateName] ),
ALLSELECTED ( ‘DWH DimDate’[DateName] )
)
VAR Mianownik =
CALCULATE (
‘DWH V_FactInventoryStore_SK_Calculated’[DM_Store_OnHand_EOP] + [DM_DIFF_Store_BlockForTransfer]
+ IF (
( [DM_Store_BlockForTransfer_EOP] - [DM_DIFF_Store_BlockForTransfer] ) < 0,
0,
[DM_Store_BlockForTransfer_EOP] - [DM_DIFF_Store_BlockForTransfer]
) + ‘DWH V_FactInventoryStore_SK_Calculated’[DM_DIFF_Store_onLocalization],
FILTER ( ‘DWH DimDate’, ‘DWH DimDate’[DateName] = minDN )
)
- CALCULATE (
‘DWH V_FactInventoryStore_SK_Calculated’[DM_Store_OnHand_EOP],
FILTER ( ‘DWH DimDate’, ‘DWH DimDate’[DateName] = maxDN )
)
VAR licznik =
Mianownik
- CALCULATE (
IF (
( [DM_Store_BlockForTransfer_EOP] - [DM_DIFF_Store_BlockForTransfer] ) < 0,
0,
[DM_Store_BlockForTransfer_EOP] - [DM_DIFF_Store_BlockForTransfer]
),
FILTER ( ‘DWH DimDate’, ‘DWH DimDate’[DateName] = maxDN )
)
RETURN
licznik

2

Hello, Harris, can you please attach a pbix file with anonymized data ?
Best regards

3

I can’t, it’s live connection

4

@Harris, it is hard to optimize your DAX without extra information. In order to optimize your DAX, I would need to take a look at the model (relationships, size) etc. I would also need to check the server timings and query plan. Based on the DAX formula I see, I can guess that the Formula Engine is mostly used because of the if statements. There are also other measures included in your DAX that need to be checked.
I recommend taking the time to reproduce the formulas and model in a PBIX file with dummy data.
Best regards