cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aellison Regular Visitor
Regular Visitor

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,

1 ACCEPTED SOLUTION

Accepted Solutions
ankitpatira Super Contributor
Super Contributor

Re: Passing Parameters to Query Editor from another table

@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.

 

Capture.PNG

View solution in original post

7 REPLIES 7

Re: Passing Parameters to Query Editor from another table

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.

Kris
ankitpatira Super Contributor
Super Contributor

Re: Passing Parameters to Query Editor from another table

@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.

 

Capture.PNG

View solution in original post

Moderator v-qiuyu-msft
Moderator

Re: Passing Parameters to Query Editor from another 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".

z1.PNG

 

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.

z2.PNGz3.PNG

 

3. Create two query parameters, one bind to the output of MAX value, the other binds to the output of the Min value.

z5.PNG

 

4. Use these two query parameters to filter data.

z4.PNG

 

 

Reference:
Data-Driven Power BI Desktop Parameters Using List Queries

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
blopez11 Established Member
Established Member

Re: Passing Parameters to Query Editor from another table

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...

 

Jcasadoa Frequent Visitor
Frequent Visitor

Re: Passing Parameters to Query Editor from another 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?

 

powerbi.png

mbdtz Regular Visitor
Regular Visitor

Re: Passing Parameters to Query Editor from another table

@Jcasadoa I had this issue too, fixed it by transforming to a list

 

2018-06-14 13_04_53-sharepoint_trial - Power Query Editor.png

hope that helps 🙂

Highlighted
fhendrick Frequent Visitor
Frequent Visitor

Re: Passing Parameters to Query Editor from another table

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors