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.
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?
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:
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
I use foreach function to include the object in the calculation and forall to exclude the objects from the calculation