Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have a date table ('Date Table'[Date]) connected to a date column of irregular start dates ('Table'[Date]).
For example, the irregular start dates could be:
1/31/2017
4/30/2017
8/31/2017
If I make an active relationship between 'Date Table'[Date] and 'Table'[Date], I run into this issue:
I am using a date slicer on 'Date Table[Date]' to select an "as of" date to run the report for. I want LASTDATE('Date Table'[Date]) to return the last date selected on the slicer. However, because of the relationship with 'Table,' it will instead only return one of the values 1/31/2017, 4/30/2017, or 8/31/2017.
The goal of the LASTDATE function is to implement the test: "is the selected date on the slicer on or after the start date for this column? Return TRUE or FALSE"
How do I get around this but keep the relationship active?
What's the full measure you are using?
And keep in mind that LASTDATE is a table function which will transition a row context to a filter context....so having said that, you may want to use MAX ( 'Date Table'[Date] ) instead.
For all intents and purposes, the full measure is just LASTDATE('Date Table'[Date]), which in this case returns the same thing as MAX()
If I have my date slicer set for all dates on or before 9/30/2017, you would expect LASTDATE() to return 9/30/2017 on the date table.
However, in this case, it returns 8/31/2017 because it is last date that appears.
I should note that I am using a calculated column rather than a measure.
Calculated column for what part? You are keeping in mind that Calculated columns are computed during data refresh, and not at run time. A slicer based on a calculated column is fine; but a slicer won't trigger a re-calculation of a calculated column.
can you share your measures/calc column and sample data, and also relationship model. it will help to debug
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.
User | Count |
---|---|
88 | |
72 | |
69 | |
65 | |
57 |
User | Count |
---|---|
95 | |
93 | |
91 | |
75 | |
69 |