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

Double lookup and conditional formatting

Hello,ve

this is the table I have:

[CMF]

obs date (MM/DD/YYYY)Creation RICPRICEOUTLIAR [True/False]
10/14/2019 53.640
10/14/2019 53.660
10/15/2019 52.870
10/15/2019 52.810
10/16/2019 53.440
10/16/2019 53.430
10/17/2019X9 & Z956.241
10/17/2019 53.440
10/18/2019 53.860
10/18/2019 53.530
10/19/2019 53.510
10/19/2019 54.050
10/20/2019 54.480
10/20/2019 55.970

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)RICPriceOUTLIAR - example/wanted result
10/14/2019X953.59 
10/14/2019Y952.81 
10/14/2019Z953.36 
10/15/2019X953.51 
10/15/2019Y954.48 
10/15/2019Z955.97 
10/16/2019X955.25 
10/16/2019Y953.43 
10/16/2019Z954.04 
10/17/2019X955.00               55.00
10/17/2019Y952.4 
10/17/2019Z953.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 ->

 

Capture.PNGraws 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

3 REPLIES 3
Community Support Team
Community Support Team

Re: Double lookup and conditional formatting

Hi @carbluc 

First split column to rows, then trim and clean the column
Capture6.JPG
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
Capture7.JPGCapture8.JPG
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.
Highlighted
carbluc Frequent Visitor
Frequent Visitor

Re: Double lookup and conditional formatting scatter plot

Hello  @v-juanli-msft 

 

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

 

would you like a pbix file?

to recap:

 

[RAW]

 

obs_datepricericsettlemnt_date
10/21/201953.51CLZ911/20/2019
10/22/201954.48CLZ911/20/2019
10/23/201955.97CLZ911/20/2019
10/24/201956.23CLZ911/20/2019

[...]

10/21/201953.54CLF012/19/2019
10/22/201954.5CLF012/19/2019
10/23/201955.99CLF012/19/2019
10/24/201956.27CLF012/19/2019

 

[CMF]

 

idobs_datepriceRIC BeforeRIC AfterCreation RICoutlierCalculated Settlement
30007151310/21/2019 $   53.51CLZ9CLF0CLZ9 & CLF0011/21/2019
30007151310/22/2019 $   54.48CLZ9CLF0CLZ9 & CLF0011/22/2019
30007151310/23/2019 $   55.97CLZ9CLF0CLZ9 & CLF0011/23/2019
30007151310/24/2019 $   56.24CLZ9CLF0CLZ9 & CLF0111/24/2019

...

30016716910/21/2019 $ 51.67CLN3CLM3CLM3 & CLN306/17/2023
30016716910/22/2019 $ 51.25CLN3CLM3CLM3 & CLN306/18/2023
30016716910/23/2019 $ 51.14CLN3CLM3CLM3 & CLN306/19/2023
30016716910/24/2019 $ 51.37CLN3CLN3CLN3 & CLN306/20/2023

 

 

### NOTE:

RIC BeforeRIC AfterCreation 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:

image.pngugly but useful viz of those RIC corresponding to outlier

 

Thanks, @v-juanli-msft 

Luca.

Community Support Team
Community Support Team

Re: Double lookup and conditional formatting scatter plot

Hi @carbluc 

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.

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (2,234)