WEBI find duplicate rows (with exception of certain columns) and highlight

Question

consider the following table:

+------+------+----------+----------------+
| Col1 | Col2 |   Col3   | Numeric Column |
+------+------+----------+----------------+
| ValA | ABC  | Value 3  |            101 |
| ValF | DEF  | Value 10 |            101 |
| ValC | DEF  | Value 10 |            101 |
| ValB | GHI  | Value 12 |            103 |
+------+------+----------+----------------+

I would like to find duplicate rows by comparing values across multiple columns, and highlight the values in the [Col1] column when duplicate rows are found (OR highlight the whole row, whatever is easier). So in the above table I would like to compare values of the [Col2], [Col3] and [Numeric Column] columns.

And in this example, ValF and ValC in [Col1] would be highlighted. I am not sure how to go about this.

score 0 · Accepted Answer · answered Aug 17 '21 at 07:46

I figured it out, see steps below.

Create a dimension variable and concatenate all the columns you like to compare into a variable.

Variable name: Concat_col
Variable formula: =[Col2]+[Col3]+[Numeric Column]

Create a measure variable and refer to the first variable.

Variable name: Count_col
Variable formula: =RunningCount(NoFilter([Concat_col]);([Concat_col]))

Create a measure variable and refer to the first and second variable.

Variable name: Max_col
Variable formula: =Max(NoFilter([Count_col])) In ([Concat_col])

Now create a Formatting rule:

And populate as follow:

To apply to a whole row, select the columns one by one and click Formatting Rules > 'Conditional Format' (this is the name of the formatting rule we just created earlier).

WEBI find duplicate rows (with exception of certain columns) and highlight

1 Answers1