Frequent Visitor
Posts: 8
Registered: ‎03-10-2017

Page Level Filter by Max Date - Direct Query

We are trying to create a page level filter on the MAX(Date). By default the page level filters do not have the "Latest" filter like when you're creating a card visual. We have tried creating a MEASURE for the "Latest Date" and then creating a Calculated Column where if the DATE = Latest Date then it's true and if not false among many other suggestions from the web. It seems that since we are working in Direct Query mode, many of the suggestions do not work.


Latest Date = MAX([Date])

Is Latest = IF([Date] = [Latest Date], TRUE(), FALSE())

        ERROR: Function 'MAX' is not allowed as part of calculated column DAX expressions on DirectQuery models.


Our data looks like the following and with the desired functionality we will only be looking at the BOLDED records.

IDDateMiss PgMiss VisCleanLockedSigned
103/21/17 0:00YESYESYESNONO
93/21/17 0:00NONOYESNONO
83/21/17 0:00NONONOYESYES
63/20/17 0:00YESYESNONONO
53/20/17 0:00NONOYESYESYES
43/19/17 0:00NONOYESNONO
23/19/17 0:00YESYESYESNONO
13/19/17 0:00NONOYESNONO
New Contributor
Posts: 646
Registered: ‎07-22-2015

Re: Page Level Filter by Max Date - Direct Query

Latest date is always today's date or latest date is based on the last record in the table, please clarify?

Frequent Visitor
Posts: 8
Registered: ‎03-10-2017

Re: Page Level Filter by Max Date - Direct Query

[ Edited ]

@parry2k, actually neither. "Latest" means the most recent date in the date column. It will not always be today and the records may not be in order so it can't be the "last" record. It should be equivalent to MAX() or LATEST() in terms of a datetime.