cancel
Showing results for
Did you mean:
Frequent Visitor

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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

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

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.

Community Support Team

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

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.

Announcements