I have a measure in a data model that returns the date of the oldest record in the data-set.
Manually I put this date in the following line of a query in the same data model (not the same query):
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [Return Month] < #date(2018, 5, 1))
Is there a way to reference the output of the measure in the #date expression so that I don't need to key it manually? It's fine for me to do it, but my users really don't know how to open and edit M.
Many thanks for any help you can offer.
Lindsay
Hi Simon,
You say measure so you are talking about the result of a DAX calculation?
There's no way to refer to the measure from inside the PQ editor I'm afraid.
Can't you work out the date of the oldest record using PQ and get the value that way?
regards
Phil
Thanks Phil, I'll look at how to make your suggestion work.
All the best
Simon
Thank you for your help Phil.
It was so easy in PQ (once I explored and made a few howlers).
Duplicate the query generating the large data-set (call it 'Oldest_Record', delete all columns but the date column, sort on earliest, delete duplicates, drill down and change to text.
The query that I had been updating manually now becomes:
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [Return Month] < Date.FromText((Oldest_Record)))
... so now I don't have to worry about my users editing a query - they just press a button.
It just goes to show, everything is easy, when you know the answer!
Much appreciated,
Simon
No worries Simon. When you start using PQ a lot you see how awesome it is, it'll change your life.
regards
Phil
