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

[Anonymous]

Hello coworker,

my [CMF] table is:

Obs date	outlier	Ric Before (calculated column)	Ric After (calculated column)
11/12/2019	0	CLF0	CLG0
11/12/2019	0	CLF1	CLG1
11/12/2019	0	CLF2	CLG2
11/12/2019	0	CLF24	CLG24
11/12/2019	1	CLF3	CLG3
11/12/2019	0	CLG0	CLH0
11/12/2019	0	CLG1	CLH1
11/12/2019	0	CLG2	CLH2
11/12/2019	0	CLG24	CLH24
11/11/2019	1	CLF3	CLH3
11/11/2019	0	CLH0	CLJ0
11/11/2019	0	CLH1	CLJ1

Then I have the [RAW] table ->

Obs date	RIC	price	Wanted column - RawOutlier
11/12/2019	CLF0	56.85
11/12/2019	CLF1	53.37
11/12/2019	CLF2	51.94
11/12/2019	CLF24	51.66
11/12/2019	CLG3	52.12	52.12
11/12/2019	CLF26	52.52
11/12/2019	CLF27	52.67
11/12/2019	CLF28	52.63
11/12/2019	CLF29	52.63
11/12/2019	CLF3	51.5
11/12/2019	CLF30	52.63
11/12/2019	CLG0	56.74
11/11/2019	CLG1	53.17
11/11/2019	CLG2	51.84
11/11/2019	CLG24	51.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.

[v-yuta-msft · Accepted solution]

@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.