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.
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,
Solved! Go to Solution.
@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.
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 Queries
Best Regards,
Qiuyun Yu
As 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-da...
@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.
The field query is not editable for me.
I have a query available but I can't select anything at all.
Does anyone know what can I be doing wrong?
Hi guys,
I'm missing something out...
I did what you described, but my parameter value is still keeping the "Current Value" field content instead of updating with the Query value I placed in "Query" field.
It's like regardless the stuff I place in "Query" field, the parameter is stuck on the "Current Value".
Could you help ?
Thanks
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.
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |