Query folding using a previous query to filter

[Aaron301]

Hello. I am working with a dataflow gen2 in Fabric and trying to filter my main query by a max date that I get from my lakehosue. When I do this the query stops folding so I had to insert a step to filter by a static number of days before it to make it manageable as a workaround.

Is there anyway to do this in a way that the query folds?

Thanks in advance!

Last_Activity_Date:

Main Query: 

[GilbertQ]

Hi @Aaron301 

In your dataflow gen2 you could use the table called "Last_Activity_Date" and on the table right click and select Drill Down. This will result in the table having a single value which you can then re-use.

Then in your Main query where you have got the filter you could use the table name as the filter.

This is what you are looking for after the drill down

[Aaron301]

Thanks for the quick reply but Last_Activity_Date is already returning a single value as a DateTime and I am already calling that in the main query as you said.

I know that you can't append queries without breaking folding but it seems silly that it wouldn't be able to use the value from the first query as a variable in the Where predicate so I feel like I must be doing something wrong.

I copied and pasted the value returned from the first query, converted it to a DateTime and replaced the call to it in the main query with it and it folds. If I use the DateTime returned from Last_Activity_Date the data returned is correct but it doesn't fold.

Folds:

Doesn't fold: