Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All ,
I have the following use case :
1. There are two tables one fact table and other is calendar table / Date table.
2. Both the tables come from Database using Direct query.
3. I Have to take the max date from fact table and and pass that date to Calendar table by filtering the date column while fetching the data. (I should be able to filter the data in Calendar table for only dates less than or equeal to the max date of the fact table).
I have created a reference table of fact table and in transform selected the latest date.
I have no clue was has to be done ahead.
Can anyone please help me to achieve this.
I referred the following link :
https://www.ehansalytics.com/blog/2019/3/17/create-a-dynamic-date-table-in-power-query
@powerquery
Solved! Go to Solution.
Hi All ,
Got through the appropriate answer to it :
While fetchoing the table using direct query we transformed and set the filter on the date
sample filter code :
table.selectedrows(Calendar,each[date] <List.Max (date) ))
Hi All ,
Got through the appropriate answer to it :
While fetchoing the table using direct query we transformed and set the filter on the date
sample filter code :
table.selectedrows(Calendar,each[date] <List.Max (date) ))
Hi there, it's been a while, but can you please tell where do you enter this code? and here which is the fact table & the date table? table.selectedrows(Calendar,each[date] <List.Max (date) ))
I have the same problem - have to limit the date table according to the max date in fact table in Power query
Thanks in advance!
3. I Have to take the max date from fact table and and pass that date to Calendar table by filtering the date column while fetching the data. (I should be able to filter the data in Calendar table for only dates less than or equeal to the max date of the fact table).
You _can_ do that by allowing the search filter to flow from the fact to the dimension. But that is highly discouraged in a star schema. It will probably be easier to generate a measure and use that for the extract.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |