The final group of M queries, parameter table queries, are developed for usability and manageability purposes. From a usability standpoint, the Date Intelligence Metrics and Adventure Works Sales queries serve to consolidate similar DAX measures in the Fields list. Additionally, the CurrentDate query is used to provide reports with a text message advising of the latest data refresh date. From a manageability standpoint, the Measure Support query can be used to centralize intermediate or branching DAX expressions that can be referenced by many DAX measures.

As shown in the following example of the Adventure Works Sales query, a trivial expression can be used for three of the four queries since the purpose of the query is simply to provide a table name to the data model:

Adventure Works Sales Parameter Tables query

The Date Intelligence Metrics, Adventure Works Sales, and Measure Support queries can all retrieve a blank value and the Include in report refresh property can be disabled. The following two chapters will demonstrate how these blank tables can be utilized as data model metadata, and DAX measures are added to the dataset in Chapter 9, Designing Import and DirectQuery Data Models, and Chapter 10, Developing DAX Measures and Security Roles, respectively.

The CurrentDate query is the only parameter table query that needs to be executed with each report refresh. The following M script for the CurrentDate query produces a table with one column and one record, representing the current date as of the time of execution:

let
    RefreshDateTime = DateTime.LocalNow(),
    TimeZoneOffset = -5,
    RefreshDateTimeAdjusted = RefreshDateTime + #duration(0,TimeZoneOffset,0,0),
    RefreshDateAdjusted = DateTime.Date(RefreshDateTimeAdjusted),
    TableCreate = Table.FromRecords({[CurrentDate = RefreshDateAdjusted]}),
    DateType = Table.TransformColumnTypes(TableCreate,{"CurrentDate", type date})
in
    DateType

All reported times in Microsoft Azure are expressed in Coordinated Universal Time (UTC). Therefore, timezone adjustment logic can be built into the M query to ensure the last refreshed date message reflects the local timezone. In the preceding example, five hours are reduced from the DateTime.LocalNow() function reflecting the variance between US Eastern Standard Time and UTC. The adjusted datetime value is then converted into a date value and a table is built based on this modified date value.

As shown in the following image, the Adventure Works Sales and Date Intelligence Metrics queries are represented in the FIELDS list and the CurrentDate query is used by a DAX measure to advise of the last refreshed date:

Parameter Tables in Fields list and Data Refresh Message

The DAX expression supporting the last refreshed message is as follows:

Last Refresh Msg = 
  VAR CurrentDateValue = MAX('CurrentDate'[CurrentDate])
  RETURN "Last Refreshed: " & CurrentDateValue

An additional example of using DAX to return a string value for title or label purposes is included in the Drillthrough Report Pages section of Chapter 12, Applying Custom Visuals, Animation, and Analytics.

As datasets grow larger and more complex, BI teams or dataset designers may add or revise group names to better organize M queries. For example, the four parameter group queries in this section serve three separate functions (fields list, last refreshed date, and DAX logic centralization).

To experienced Power BI and SSAS Tabular developers, a parameter table is understood as a custom table of parameter values loaded to a model and exposed to the reporting interface. DAX measures can be authored to detect which value (parameter) has been selected by the user (for example, 10% growth, 20% growth) and dynamically compute the corresponding result. For this dataset, the concept of Parameter Tables is extended to include any query that is loaded to the data model but not related to any other table in the data model.