Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
aellison
Helper I
Helper I

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
ankitpatira
Community Champion
Community Champion

@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
v-qiuyu-msft
Community Support
Community Support

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.

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

 

ankitpatira
Community Champion
Community Champion

@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

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

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

Anonymous
Not applicable

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

a_mixed_life
Resolver I
Resolver I

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.