Skip to Content

Highlight duplicate value in tow different blocks

Sep 25, 2017 at 02:04 PM


avatar image


Highlight duplicate value in tow different blocks. Please refer to the image attached. Just wanted to know if this is possible.

capture.png (7.8 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
Koen Hesters Sep 25, 2017 at 02:13 PM

Yes it is, but you will have to merge your common dimension and create a variable or 2 and add conditional formatting

Show 5 Share
10 |10000 characters needed characters left characters exceeded

Thanks Koen. I am using the merged dimension.

Can you please elaborate the solution in little detail?



I resolved it by doing this:
  1. Create merge on SKU number...
  2. if you setup the tables as MErged Dimension & Query 2.SKUNumber .. you will see in second column SKU number of query 2 that is contains n° 120.114, but actually it does not, it is because of the merge, so that cannot be used for comparison
  3. I had to call two extra variables to do so. I used a dimension that always has an entry for the SKU number and used them to compare. (ie. Lines)

I created 2 detail variables associated to the merged dimension and I created a third one to compare the 2 detail variables:

=If  IsNull([check Query 1])=0 And IsNull([check Query 2])=0 Then "EQ" Else "NE"

If you set your tables like the first screenshot, you 'll see the data per Query and you will be able to highlight the values common in both tables

conditional Format set on the SKU-Number columns - you can hide the not wanted columns as well

capture.png (22.0 kB)
capture.png (12.7 kB)
capture.png (22.6 kB)
capture.png (19.1 kB)
capture.png (30.0 kB)
capture.png (12.2 kB)
capture.png (30.0 kB)

Thanks Koen. Works like a charm, but unfortunately when I apply a Ranking on both the table this logic doesnt work.

Just to let you know, my first table is ranked by Qty(Top 50) and second by Value(Top 50). Is it possible to do with Ranking?



in the example I play with, the ranking goes fine

I added the price and ranked by TOP3 and you see:

make sure to put the measure of Query 2 in BLock 2 (data query2) and measure of Query 1 in Block 1 (data query 1)

capture.png (18.3 kB)
Former Member


Attached the screen shot. I have query from 2 DP. one from table(Material)and another from Excel(ITEM)

I am doing an extended merge, so i can get matching as well as unmatching record

The formula i am trying =If IsNull([LWVAL])<>0 Then "A" ElseIf IsNull([V_LastWeek_Data]<>0) Then "B" ElseIf IsNull([LWVAL])<>0 And IsNull([V_LastWeek_Data]<>0) Then "C" Else "NotDefined"

1) 111 contains in both table (Material& Excel)

2)121 only contains in Excel

3)123 only contains in Material


capture1.jpg (21.3 kB)