cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
carbluc Frequent Visitor
Frequent Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

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

@carbluc ,

 

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())

Capture.PNG 

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.

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

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

@carbluc ,

 

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())

Capture.PNG 

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.

View solution in original post

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors