title: How to get correct row count from data view based on distinct values in Two Columns
body: <p>I have data view in which I need to get row count based on the Combined list of distinct values of two columns(say ColumnA and ColumnB ). I can get the String list of distinct values from these two columns. The part I am struggling with is how to get exact row count (into a List of Integer) from the dataview based on each item in the String list. I think the trouble is some values of ColumnA may not be present in ColumnB and vice versa.If I apply a row filter based on ColumnA , it is possible to miss out on some of the values ColumnB Which is why I did something like below. But I end up getting extra count in the end in my Integer list. If I dont do this then I miss out on some of the values resulting in getting lower combined total than the no. of rows.</p>
  
  <p>Please advise. I need to use the final List of String and Int values to create a basic table and then create a chart in excel. </p>
  
  <pre><code>For x As Integer = 0 To DistinctColValueList.Count - 1
      '' OR Condition Below Gives more count
      'Dv.RowFilter = "ColumnA = '" &amp; DistinctColValueList.ElementAt(x) &amp; "' OR ColumnB = '" &amp; DistinctColValueList.ElementAt(x) &amp; "'"
  
      Dv.RowFilter = "ColumnA = '" &amp; DistinctColValueList.ElementAt(x) &amp; "'"
  
      DistinctIntValueList.Add(Dv.Count)
  Next
  
  Dim ClonedList As New List(Of Integer)
  For Each item In DistinctIntValueList
      ClonedList.Add(item)
  Next
  
  Dv.RowFilter = ""
  DistinctIntValueList.Clear()
  
  For y As Integer = 0 To DistinctColValueList.Count - 1
      Dv.RowFilter = "ColumnB = '" &amp; DistinctColValueList.ElementAt(y) &amp; "'"
  
      Dim Abs_Diff = Math.Abs(ClonedList.ElementAt(y) - Dv.Count)
  
      DistinctIntValueList.Add(ClonedList.ElementAt(y) + Abs_Diff)
  Next
  </code></pre>
  
  <p>Update: added example table.</p>
  
  <pre><code>+-----------------------------+
  |Item    |Column_A  |Column B |
  |        |          |         |
  +-----------------------------+
  | XYZ1   |   Loc1   |  Loc5   |
  | XYZ2   |   Loc13  |  Loc6   |
  | XYZ3   |   Loc4   |  Loc4   |
  | XYZ4   |   Loc6   |  Loc10  |
  | XYZ5   |   Loc5   |  Loc2   |
  | XYZ6   |   Loc5   |  Loc6   |
  | XYZ7   |   Loc6   |  Loc9   |
  | XYZ8   |   Loc4   |  Loc10  |
  | XYZ9   |   Loc6   |  Loc13  |
  | XYZ10  |   Loc2   |  Loc10  |
  +--------+----------+---------+
  </code></pre>