Forum Discussion

VeemalS's avatar
VeemalS
Helper I
4 years ago
Solved

DAX Optimisation

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

 

 

  • smpa01's avatar
    smpa01
    4 years ago

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

     

     

  • VeemalS's avatar
    VeemalS
    4 years ago

    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

3 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    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. 

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

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

     

     

    • VeemalS's avatar
      VeemalS
      Helper I
      4 years ago

      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