Forum Discussion
Passing Parameters to Query Editor from another table
I have a table imported to PowerBI Desktop that will end up in the powerbi.com portal. This table has a DateKey column in it (we'll call this OrderDateKey). I am going to be joining that to a DateDim table, but unless I have to, I don't want to pull ALL of the data from the DimDate table, only the dates that are between the MIN and MAX of the OrderDateKey, which makes the slicers, filters, etc. easier to use with less options to choose from. I know how to set parameters to a query using static values, but not sure how to get it to use the MIN and MAX dates. These tables are not on the same server, and not planning on using a Linked Server or anything to join them prior to import.
Thanks in advance,
aellison One of the new feature for query parameters is to be able to use other queries as a source for it. So you can create two new queries that gives max and min values and set that as a source for your parameters and then use those parameters to filter DateDim table.
7 Replies
Hi aellison,
As ankitpatira suggested, you can specify the query parameter values from other list query output. In your scenario, you can create two list query which returns both MIN and MAX of the OrderDateKey. Refer to below:
1. In Query Editor, check the OrderDateKey column then select "As a New Query".
2. In the new query, check Sort Descending. Then use "Keep Top Items" to only keep the MAX value. Use the same way to get a list which contains the Min value.
3. Create two query parameters, one bind to the output of MAX value, the other binds to the output of the Min value.
4. Use these two query parameters to filter data.
Reference:
Data-Driven Power BI Desktop Parameters Using List QueriesBest Regards,
Qiuyun YuAs an alternative, I went the other way. I created my datetable to have the min of my data's datekey and max of current date (though you could change this to be max of your data). If interested you can refer to http://community.powerbi.com/t5/Desktop/Date-Dimension-Table-that-Dynamically-Pulls-Start-and-End-dates/td-p/76870
You could create a new table for DateFilter from your OrderDateKey within PowerBI instead of using a DateDim from a dwh. That way it will take the min and max value.
aellison One of the new feature for query parameters is to be able to use other queries as a source for it. So you can create two new queries that gives max and min values and set that as a source for your parameters and then use those parameters to filter DateDim table.
