Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure based on Excel formula =IF(AND(OR(...))) to DAX formula

Hello coworker,

 

my [CMF] table is:

Obs dateoutlierRic Before (calculated column)

Ric After

(calculated column)

11/12/20190CLF0CLG0
11/12/20190CLF1CLG1
11/12/20190CLF2CLG2
11/12/20190CLF24CLG24
11/12/20191CLF3CLG3
11/12/20190CLG0CLH0
11/12/20190CLG1CLH1
11/12/20190CLG2CLH2
11/12/20190CLG24CLH24
11/11/20191CLF3CLH3
11/11/20190CLH0CLJ0
11/11/20190CLH1CLJ1

 

 

Then I have the [RAW] table ->

 

Obs dateRICpriceWanted column - RawOutlier
11/12/2019CLF056.85 
11/12/2019CLF153.37 
11/12/2019CLF251.94 
11/12/2019CLF2451.66 
11/12/2019CLG352.12     52.12
11/12/2019CLF2652.52 
11/12/2019CLF2752.67 
11/12/2019CLF2852.63 
11/12/2019CLF2952.63 
11/12/2019CLF351.5 
11/12/2019CLF3052.63 
11/12/2019CLG056.74 
11/11/2019CLG153.17 
11/11/2019CLG251.84 
11/11/2019CLG2451.64 

 

the formula that I have in Excel is>

 

=IF(AND(OR(RAW[@RIC]=CMF[@[RIC Before]],RAW[@RIC]=CMF[@[RIC After]]),CMF[@outlier]=1,RAW[@[TRADING_DATE]]=CMF[@[obs_date]]),RAW[@SETTLE],"null")

 

 

that in english -> give me the Raw price if that day there is an outlier in CMF with matching RIC

 

and I would like to transforme it in dax.

 

thank you for any help,

Luca.

Need Help
Tutorial Requests
  • v-yuta-msft's avatar
    v-yuta-msft
    6 years ago

    Anonymous ,

     

    Create a calculate column in RAW table using dax below:

    RawOutlier = 
VAR Current_Date = RAW[Obs date]
VAR Current_RIC = RAW[RIC]
VAR Current_price = RAW[price]
RETURN
IF(COUNTROWS(FILTER(CMF, CMF[Obs date] = Current_Date && CMF[Ric After] = Current_RIC && CMF[outlier] = 1)) >= 1, Current_price, BLANK())

     

    You can also refer to the pbix file.

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Anonymous ,

     

    Create a calculate column in RAW table using dax below:

    RawOutlier = 
VAR Current_Date = RAW[Obs date]
VAR Current_RIC = RAW[RIC]
VAR Current_price = RAW[price]
RETURN
IF(COUNTROWS(FILTER(CMF, CMF[Obs date] = Current_Date && CMF[Ric After] = Current_RIC && CMF[outlier] = 1)) >= 1, Current_price, BLANK())

     

    You can also refer to the pbix file.

     

    Community Support Team _ Jimmy Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.