Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
pzeller
Frequent Visitor

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
73/20/17 0:00YESYESNOYESYES
63/20/17 0:00YESYESNONONO
53/20/17 0:00NONOYESYESYES
43/19/17 0:00NONOYESNONO
33/19/17 0:00YESYESYESYESYES
23/19/17 0:00YESYESYESNONO
13/19/17 0:00NONOYESNONO
4 REPLIES 4
kmccormick
Frequent Visitor

Wondering if any of you have found a solution? I'm bummed that I got this to work so well in Import but can't transfer the methodology to Direct Query. Smiley Sad

valivali
Regular Visitor

I'm having the same problem, does anybody have a solution?

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.