Is one value in a column present in another column

[Anonymous]

Hi everyone! Thank you so much for taking the time to read my question and potentially help me out!

I am currently creating a dashboard where I am looking to see if one value is present in two columns.  If it is not present in the other column, I will then need to look to see how many times it occurs and if it is above a certain amount.  Please see an example below:

** So I only want have a chart that pulls back week_2 colours that were NOT in week1_colour, and if they were not listed in week1_colour, then have a value of week2_sales ABOVE 5.

Week1_colour          week1_sales               week 2_colour             week2_sales

blue                              3                                  red                                   10

red                                6                                  burgundy                         15

green                            7                                  yellow                              3

purple                          10                                 lilac                                   9

yellow                          25                                 pink                                   1

aqua                            16                                 orange                              6

So I want a table that (based on what's above), would result with:

week2_colour           week2_sales

lilac                                   9

burgundy                         15

orange                              6

** not result with blue, red, green, purple, aqua as either only present in week1 OR have a sales value that is not over 5.

What would the expression be that I plug into the chart/how would I be able to go about doing this?

Many thanks again!

[MK_QSL]

It would be better if you provide source data or sample application.

[sunny_talwar · Accepted solution]

May be something like this:

Dimension

week 2_colour

Expression

Sum({<[week 2_colour] = e(Week1_colour)>} week2_sales)

[nicolas66]

Bonsoir,

may be:

T1:

LOAD Week1_colou,

     week1_sales

FROM

(ooxml, embedded labels, table is Feuil1);

T2:

load*

where week2_sales>5

;

LOAD [week 2_colour],

     week2_sales

FROM

(ooxml, embedded labels, table is Feuil2)

where not Exists (Week1_colou,[week 2_colour])

;

drop table T1;