Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Query for parameter suggested values (global variable for entire project?)

We are trying to set global variables StartDate and EndDate that all queries can references as a filter.   I would like to set StartDate to this TSQL formula SELECT DATEADD(MONTH, -12, DATEADD(MONT...
Need Help
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi Anonymous,

You can perform the following steps to populate the parameter list based on a query.

1. Import a table from SQL Server by writing your T-SQL in SQL statement(optional) textbox as shown in the following screenshot.


2. Get a list from the date column in the imported table,  you would need to click on the column to select it and then right-click and select “Add as New Query”. Now you have a list named Query 3 whose values are derived from your T-SQL formula.


3.When you create new parameter, you will be able to choose Query 3 as follows.


For more details about how to create query parameter in Power BI Desktop, please review the following blog.
Data-Driven Power BI Desktop Parameters Using List Queries


Thanks,
Lydia Zhang

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Thank you for the feedback, still encountering problems. I followed instructions as noted in screenshot below and the Query dropdown box still won't accept values. Is there anything else I'm missing?

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi Anonymous,

    Based on your screenshot, you are missing the step that get a list from the date column in your table. As stated in the blog I have post, To get a list from a column in your table,  you need to click on the column to select it and then right-click and select either:

    • Drill Down
    • Add as New Query

    After you successfully get a list, you can have a list mark which looks like as follows.


    Thanks,
    Lydia Zhang