SSRS Expression to count the distinct number of items visible

Question

Currently I'm using this expression in a cell above the column headers of a table:

= "Num Days " & CountDistinct(Fields!Date.Value)

In the report designer this is the context of the expression:

Here is what the result looks like:

The expression is working ok i.e in the data set there are 60 distinct dates which is what the expression returns. However there is a filter on the group (Date2) so that it only has 10 visible days in the tablix.

How do I amend the expression to return just the dates that are visible?

Answer 1

The easiest solution is to move the filter upstream: can you put the filter on the Dataset instead of the Tablix Group? Then these rows won't be included in your total.

Other options include custom code to keep a running tally of values, or putting a conditional in your aggregate expression, maybe something like:

   = "Num Days " &
     (COUNTDISTINCT( IIF(DateDiff("d", Fields!Date.Value, Now) <= 10,  Fields!Date.Value, 1)) -1 )