Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
@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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |