1

I have data looks like below, and am grouping at Column1. I need to compare column2 with in Group and highlight if the values for column2 is same or different in Column3-

Column1 Column2 Column3
123     111 
123     111 
1234    2222    
1234    2222    
1234    3333

I am using expression in Column3 as below. As you can at last "Column1" I have mentioned it considering it will group and then compare.

=IIF(Fields!Column2.Value = Previous(Fields!Column2.Value), "Same", IIF (Fields!Column2.Value <> Previous(Fields!Column2.Value), "Different")), "Column1"

My expectation is as below, i.e. column3 should populate if all value of Column2 is same under a group (column1 grouping) then Column3 should populare same else different for all rows under a group

Column1 Column2 Column3
123      111    Same
123      111    Same
1234    2222    Different
1234    2222    Different
1234    3333    Different
AnkUser
  • 5,421
  • 2
  • 9
  • 25
Janny
  • 25
  • 7

2 Answers2

1

You can use CountDistinct for this.

If we assume your Column1 'RowGroup' is called grpCol1 then the expression in column 3 would look something liek this.

= IIF(CountDistinct(Fields!Column2.Value,"grpCol1") >1 , "Different", "Same")

Basically this says.. Count how many different Column2 values there are within the the row group grpCol1

So for the first two rows in your example it would return 1 as there is only 1 distinct value, for the next three rows, it would return 2 as there are 2 distinct values.

Note: grpCol1 or whatever your row group is called must be within qoutes and is case sensitive.

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • Hi Alan Schofield, Yes It is working perfectly fine thanks for the solution It is really a great help. I can say now this platform actually helps. Many thanks for your time. – Janny May 23 '19 at 10:03
0

A simpler way would be:

=IIF(Fields!Column2.Value = Fields!Column1.Value, "Same", "Different")
SuperSimmer 44
  • 964
  • 2
  • 7
  • 12
  • Hi, this is not about comparing Column1 and Column2. It is about Grouping happening at Column1 i.e.123 is a group of 2 first rows, and then it need to compare column2 value under the same group i.e. 123 if it is same of different – Janny May 23 '19 at 06:42