Filtering a DISTINCTCOUNT in DAX

Question

I have what I thought was a simple task: create a Measure that returns the distinct count of a field for all records that meet a specific filter condition, in this case a date filter. I came up with the following:

=CALCULATE(DISTINCTCOUNT(ExitFilter[Dim_Client_ID]),ExitFilter[ExitDate]<1/1/2014)

and the result was (blank) so I tried:

=CALCULATE(DISTINCTCOUNT(ExitFilter[Dim_Client_ID]),FILTER(ExitFilter,ExitFilter[ExitDate]<1/1/2014))

and the result was still (blank) so I tried:

=CALCULATE(COUNTROWS(DISTINCT(ExitFilter[Dim_Client_ID])),FILTER(ExitFilter,ExitFilter[ExitDate]<1/1/2014))

and the result was still (blank). What am I missing? And what does (blank) signify?

If it's not clear 'ExitFilter' is a table, [Dim_Client_Id] is a column, and [ExitDate] is a date column.

Answer 1

Tom, comparing dates is from my experience best done with simple Date function.

So if you update your formula to something like this:

=CALCULATE([Your Measure], Table[DateColumn]>DATE(2014,1,1))

You will get what you need with easy-to-read code. Also, for any advanced time-based calculations, the best way to go is creating dates table and using Time Intelligence module of PowerPivot.

Answer 2

Tom, think your second attempt is 'right' but you need to be careful about how you are expressing the date.

Later versions PowerPivot are a little bit funny about this and while "01/01/2014" should work I don't think it will.

Instead try replacing 01/01/2014 with 41640 - this is is the underlying date value, its hacky but then so is any kind of hard coded date :-)