Generating a list of parameter values via queries
The parameter values for selection, such as dates and product subcategories, can also be parameterized via M queries. This data-driven approach to exposes the current or relevant values from data sources and avoids error-prone manual entry, and stale or outdated values.
This recipe includes two examples of query-driven parameter values. One example retrieves the week end dates from the prior two years and another selects the product subcategories of a product category.
How to do it...
Dynamic date parameter query
- In the Query Editor, create a new query and name it
WeekEndDatesParamList.
- Reference the existing date dimension query and use standard M functions to select the week ending date column and the dynamic
Calendar Month Statuscolumn described in Chapter 5, Creating Power BI Dashboards and Chapter 6, Getting Serious with Date Intelligence:
let DateColSelect = Table.SelectColumns(Date,{"Calendar Month Status","Calendar Week Ending Date...
