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.
Hello,
I'm struggling to filter a database based on a date listed in another database.
I've seen similar problems here, but so far nothing worked, or I'm getting the coding wrong.
I have a database called Existencia, with several rows and three columns. One of the columns has dates, column Dat1.
I have another database, called Analise, and I want to filter the date column - called Dia - in the database Analise, to be on or before the latest date from Dat1 in the Existencia database. So I want -----> Dia <= latest Dat1
Note: The latest date is constantly changing, the Existencia database source is an Excel file, so new rows created constantly.
I don't want to merge or append, and want to avoid creating another column in the Analise query to meet the condition.
But I'm struggling with the language to filter in the Advanced Editor.
Solved! Go to Solution.
Hi @AnaBraganca ,
I think you can use Parameter in Power Query.
Then you can choose Date Filters and select Before.
Finally you can get the whole days before what you need.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @AnaBraganca ,
I create two tables as you mentioned, their names are Existencia and Analise.
Then I create a new table and it satisfies your requirements.
Table =
VAR LatestDate =
MAX ( 'Existencia'[Date1] )
RETURN
FILTER ( 'Analise', 'Analise'[Dia] <= LatestDate )
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
I'm trying to avoid creating another table.
What I'm trying is for this filter to be each [Data] <= the latest date of the Dat1 column (second picture). But this second part of the code is the one I'm struggling with.
This is the table with the date I want to filter with.
In this case, I want the code to filter by 30-06-2023, which will change when a new row is added.
Hi @AnaBraganca ,
I think you can use Parameter in Power Query.
Then you can choose Date Filters and select Before.
Finally you can get the whole days before what you need.
Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I've tried this before going for the Advanced Editor option, but I could never get the parameter to automatically get the last date of the Existencia table.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |