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[...
  • 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
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.