Double lookup and conditional formatting

[Anonymous]

Hello,ve

this is the table I have:

[CMF]

obs date (MM/DD/YYYY)	Creation RIC	PRICE	OUTLIAR [True/False]
10/14/2019		53.64	0
10/14/2019		53.66	0
10/15/2019		52.87	0
10/15/2019		52.81	0
10/16/2019		53.44	0
10/16/2019		53.43	0
10/17/2019	X9 & Z9	56.24	1
10/17/2019		53.44	0
10/18/2019		53.86	0
10/18/2019		53.53	0
10/19/2019		53.51	0
10/19/2019		54.05	0
10/20/2019		54.48	0
10/20/2019		55.97	0

I already have this table, from which I could also split Creation RIC in two.

this CMF table is linked by observation date* to the second table:

[Table RAWs]

obs date (MM/DD/YYYY)	RIC	Price	OUTLIAR - example/wanted result
10/14/2019	X9	53.59
10/14/2019	Y9	52.81
10/14/2019	Z9	53.36
10/15/2019	X9	53.51
10/15/2019	Y9	54.48
10/15/2019	Z9	55.97
10/16/2019	X9	55.25
10/16/2019	Y9	53.43
10/16/2019	Z9	54.04
10/17/2019	X9	55.00	55.00
10/17/2019	Y9	52.4
10/17/2019	Z9	53.79	53.79

in the RAWs table I DON'T have the outliar column...

Do you know how could I arrive at that final output above??

such that at the end the visual could be like this ->

raws with highlighted outliers

feel free to suggest anithing,

Luca.

*tables could also be connected with same month and year of contract settlement, that I left out for a cleaner view

[v-juanli-msft]

Hi @Anonymous 

First split column to rows, then trim and clean the column

Then create calculate columns as below

lookup_outliar = LOOKUPVALUE(Table1[OUTLIAR],Table1[obs date],Table2[obs date],Table1[Creation RIC - Copy],Table2[RIC])

cate_pic = IF(Table2[lookup_outliar]=1,4, SWITCH(TRUE(),Table2[RIC]="X9",1,Table2[RIC]="Y9",2,Table2[RIC]="Z9",3))

RANK = RANKX(Table2,[RIC],,ASC,Dense)

final cate = IF([cate_pic]=4,"cate"&[cate_pic]&"_"&[RANK],"cate"&[cate_pic])

Finally, add the columns into a scatter chart,
Format the colors for each category

Please note that when we add column in the “Legend” field for any chart like line, column, scatter charts,
we are unable to find the options for color conditional formatting, it is a limitation in current version.
So we need to format the colors one by one under the data color option.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello  v-juanli-msft @v-juanli-msft 

Plese note that your solution cannot be used because Creation RIC is a calculated column and not in the query itself.

Also: 

SWITCH(TRUE(),Table2[RIC]="X9" 
is not dynamic, I have many more codes unfortunately.

would you like a pbix file?

to recap:

[RAW]

obs_date	price	ric	settlemnt_date
10/21/2019	53.51	CLZ9	11/20/2019
10/22/2019	54.48	CLZ9	11/20/2019
10/23/2019	55.97	CLZ9	11/20/2019
10/24/2019	56.23	CLZ9	11/20/2019

[...]

10/21/2019	53.54	CLF0	12/19/2019
10/22/2019	54.5	CLF0	12/19/2019
10/23/2019	55.99	CLF0	12/19/2019
10/24/2019	56.27	CLF0	12/19/2019

[CMF]

id	obs_date	price	RIC Before	RIC After	Creation RIC	outlier	Calculated Settlement
300071513	10/21/2019	$   53.51	CLZ9	CLF0	CLZ9 & CLF0	0	11/21/2019
300071513	10/22/2019	$   54.48	CLZ9	CLF0	CLZ9 & CLF0	0	11/22/2019
300071513	10/23/2019	$   55.97	CLZ9	CLF0	CLZ9 & CLF0	0	11/23/2019
300071513	10/24/2019	$   56.24	CLZ9	CLF0	CLZ9 & CLF0	1	11/24/2019

...

300167169	10/21/2019	$ 51.67	CLN3	CLM3	CLM3 & CLN3	0	6/17/2023
300167169	10/22/2019	$ 51.25	CLN3	CLM3	CLM3 & CLN3	0	6/18/2023
300167169	10/23/2019	$ 51.14	CLN3	CLM3	CLM3 & CLN3	0	6/19/2023
300167169	10/24/2019	$ 51.37	CLN3	CLN3	CLN3 & CLN3	0	6/20/2023

### NOTE:

RIC Before

RIC After

Creation RIC

Are CALCULATED COLUM - so after the query is done.

For instance:

RIC After = 
LOOKUPVALUE (
    Raw[ric],
    Raw[settlemnt_date], CMF[Contract_Date_After]
)

Because the creation RIC are exacly those because they have settlement exactly before and after the calculated settlement date of each CMF, that is why 2 are taken as "creation" 

And:

Contract_Date_After = 
 CALCULATE(
     MIN(RAW[settlemnt_date]),
     FILTER(RAW, 
     RAW[settlemnt_date] >= CMF[Calculated Settlement Date]))

so there is a lot going on after the wueries are calculated, I hope you undestand the challenge.

Let me know if I can clarify in anyway and I realy appreciate your effort.

to have at least somethink like this:

ugly but useful viz of those RIC corresponding to outlier

Thanks, @v-juanli-msft 

Luca.

[v-juanli-msft]

Hi @Anonymous 

Based on my knowledge, the final visual seems impossible to achieve in Power BI currently,

especially when you have lots of categories( not only serveral ones "X9","Y9",,ect)

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.