Conditional formatting on all duplicates

I'm trying to make a formatting rule in Business Intelligence/Web Intelligence that changes background color of all duplicates based on one field (address id). The best I've got so far is this:

=Previous([Address Id])=[Address Id]

But that code only changes the background color on the following duplicate only, not the duplicate before it.

Example:

Addr.Id   Name
123       John
234       Henry [not marked]
234       Harold [marked]
345       Lucas

I want to change the background color of both of the rows with Addr.Id=234. My Google Fu hasn't come up with any info on how to compare the self-value with the next value. I only found information about "Relative Value" but this require slicing by dimension (which doesn't work for me).

Is this possible?

Answers


If you want the duplicate IDs indicated then the simplest way may be to select the Addr.Id column and apply the built in Duplicate Values... This will include other 234 if present elsewhere in the list.

If you want the cells with Henry and Harold indicated then, assuming these are in B3 and B4, select ColumnB other than B1 and apply the rule:

=OR(A2=A1,A2=A3)  

This will not include a name name associated with another 234 in the list (unless that 234 is also immediately above or below a 234).


I am able to achieve the above requirement using the following functions Count(), Foreach() and Forall() and conditional formatting

Considering above example:

Obj 1: Count(Name)Foreach(Addr.ID)
Obj 2: If obj 1 <> 1 then 1 else 0
Conditional formatting Rule: obj 2 =1 highlight the cell

Note: When other columns are included in the table use

Sum(Count(Name)Foreach(Addr.ID))ForAll(Column1;Column2)

I use foreach function to include the object in the calculation and forall to exclude the objects from the calculation


Need Your Help

Adding MFC to existing Win32 C++ Causing Errors/Crashes

c++ mfc linker visual-studio-2013 access-violation

I really want to use some features from MFC but its been a pain to get working.

Move assignment operator, move constructor

c++11 move-semantics move-constructor rule-of-three move-assignment-operator

I've been trying to nail down the rule of 5, but most of the information online is vastly over-complicated, and the example codes differ.