topic DAX Optimisation in DAX Commands and Tips

DAX Optimisation

VeemalS — Tue, 11 Jan 2022 11:33:06 GMT

Hello,

I am trying to create a new column as follows:

DeductionFlag =
SWITCH(TRUE(),
CONTAINSSTRING(RELATED(IATAFareBasis[iatafarebasisCode]),"IN") && CONTAINSSTRING(RELATED(FareReduction[FareReductionCode]),"AD"),1,
CONTAINSSTRING(RELATED(IATAFareBasis[iatafarebasisCode]),"AD") && NOT( CONTAINSSTRING(RELATED(IATAFareBasis[iatafarebasisCode]),"AD50")),1,
CONTAINSSTRING(RELATED(IATAFareBasis[iatafarebasisCode]),"ID") && NOT( CONTAINSSTRING(RELATED(IATAFareBasis[iatafarebasisCode]),"ID50")),1,
CONTAINSSTRING(RELATED(IATAFareBasis[iatafarebasisCode]),"BP") && NOT( CONTAINSSTRING(RELATED(IATAFareBasis[iatafarebasisCode]),"BPR")),1,
RELATED(IATAFareBasis[iatafarebasisCode]) = "Y" || RELATED(IATAFareBasis[iatafarebasisCode]) = "N",1,
RIGHT(RELATED(IATAFareBasis[iatafarebasisCode]),2)= "DH",1,
CONTAINSSTRING(RELATED(IATAFareBasis[iatafarebasisCode]),"RG90") || CONTAINSSTRING(RELATED(IATAFareBasis[iatafarebasisCode]),"RG75"),1,
RELATED(FareReduction[FareReductionCode]) = "IN" || RELATED(FareReduction[FareReductionCode]) = "TS" || RELATED(FareReduction[FareReductionCode]) = "PS",1,
Related(FareType[FareTypeKey]) = 55 || related(FareType[FareTypeKey]) = 134,1,
'Pax Revenue'[PAX_Rev excl_YQ ( EUR )] =0 ,1,
0)

Basically, I need that deduction flag to build measures in the model.

However, I am encountering a memory issue when the above code runs.

The culprit seems to be 'Pax Revenue'[PAX_Rev excl_YQ ( EUR )] =0 ,1, which makes my laptop run out of memory.

When I remove this condition the rest of the code works fine and I have no memory issue.

[PAX_Rev excl_YQ ( EUR )] is a calculated column in one of my fact Tables.

Is there any way I can resolve the above apart from adding more memory to my laptop?

Thanks

Veemal

Re: DAX Optimisation

ValtteriN — Tue, 11 Jan 2022 12:06:02 GMT

Hi,

Regarding optimization, this kind of formula is extremly "heavy". RELATED and CONTAINSSTRING are some of the more non-optimized formulas and calculated columns are not optimal in terms of memory usage. If possible I would try to apply your desired logic into a formula. To do this a more clarified explanation on what you are trying to achieve is required.

Re: DAX Optimisation

smpa01 — Tue, 11 Jan 2022 13:50:05 GMT

VeemalS can you use variables and store the calculation results in variables and try using variables inside SWITCH?

Re: DAX Optimisation

VeemalS — Thu, 13 Jan 2022 10:27:18 GMT

The code has been changed as follows:

DeductionFlagAll =

VAR IATA_FBASIS_CODE = RELATED(IATAFareBasis[iatafarebasisCode])

VAR IATA_IN = CONTAINSSTRING(RELATED(IATAFareBasis[iatafarebasisCode]),"IN")

VAR FARE_REDN_AD = CONTAINSSTRING(RELATED(FareReduction[FareReductionCode]),"AD")

VAR IATA_AD = CONTAINSSTRING(RELATED(IATAFareBasis[iatafarebasisCode]),"AD")

VAR IATA_AD50 = NOT CONTAINSSTRING(RELATED(IATAFareBasis[iatafarebasisCode]),"AD50")

VAR IATA_ID = CONTAINSSTRING(RELATED(IATAFareBasis[iatafarebasisCode]),"ID")

VAR IATA_ID50 = NOT CONTAINSSTRING(RELATED(IATAFareBasis[iatafarebasisCode]),"ID50")

VAR IATA_BP = CONTAINSSTRING(RELATED(IATAFareBasis[iatafarebasisCode]),"BP")

VAR IATA_BPR = NOT CONTAINSSTRING(RELATED(IATAFareBasis[iatafarebasisCode]),"BPR")

VAR FARE_REDN_CODE = RELATED(FareReduction[FareReductionCode])

VAR FARETYPEKEY = Related(FareType[FareTypeKey])

VAR PAXREVEXCLYQ = 'Pax Revenue'[PAX_Rev excl_YQ (EUR)]

VAR RESULT = SWITCH (TRUE(),

IATA_IN && FARE_REDN_AD,1,

IATA_AD && IATA_AD50,1,

IATA_ID && IATA_ID50,1,

IATA_BP && IATA_BPR,1,

IATA_FBASIS_CODE = "Y" || IATA_FBASIS_CODE = "N",1,

RIGHT(IATA_FBASIS_CODE,2) = "DH",1,

CONTAINSSTRING(IATA_FBASIS_CODE,"RG90") ||CONTAINSSTRING(IATA_FBASIS_CODE,"RG75"),1,

FARE_REDN_CODE = "IN" || FARE_REDN_CODE = "TS" || FARE_REDN_CODE = "PS",1,

FARETYPEKEY = 55 || FARETYPEKEY = 134,1,

PAXREVEXCLYQ = 0,1,0)

RETURN RESULT

The above now works fine

Thanks for the tip.

Veemal