General
This is available in version 3.6 and later.
The EvaluateDaxOrDmvDatasourceExpression function evaluates a DAX or DMV expression against a PowerBI Datasource and can return the result in a number of different formats which can be useful, for example, in repeaters or cycle groups.
Syntax
The syntax of the EvaluateDaxOrDmvDatasourceExpression function is:
EvaluateDaxOrDmvDatasourceExpression ("datasource_name", "DAX or DMV _query", responseFormat)
| responseFormat | Description |
|---|---|
| 0 | Scalar - Returns either a scalar value or the first cell of a table. |
| 1 | ListOfSimpleRows - Returns a list where each entry is an object which can be used to get the rows value by either index or column name (see below). |
| 2 | ListFromSingleColumn - You can use this if your SQL returns a table with a single column of data. In this case a list of values will be returned which can be used, for example, in a repeater. |
Example 1
The following expression will return the count of tracks by the Artist that is specified in the Report Parameter Artist. Typically this will be used in an Expression Element.
EvaluateDaxOrDmvDatasourceExpression( "Music PowerBI", "EVALUATE ROW ( ""RowCount"", CALCULATE ( DISTINCTCOUNT('music_data'[track_name]), 'music_data'[artist(s)_name] = """ & [ReportParameter].[Artist] &""" ) )" , 0)
Example 2
The following expression returns a collection of rows from a table containing reciepients for a report:
EvaluateDaxOrDmvDatasourceExpression( "M&D Demo_Supporting Report", "EVALUATE 'Store Managers' ", 1 )
Lets assume you have used this expression in a cycle action and you have configured the Value Name for this to be 'Store'. Inside your cycle group you can use these to access the values in the row for the current iteration:
These would both return the Name:
[CycleValue].[Store].Value("Store Managers[Flag]")
[CycleValue].[Store].Value("Store Managers[Email]")
This would return a comma separated list of the values in the row:
[CycleValue].[Store].Value.ToCsv()
Example 3
The following expression returns a list of Stores in the database :
EvaluateDaxOrDmvDatasourceExpression("M&D Demo_Sales & Returns Sample", "EVALUATE SUMMARIZECOLUMNS('Store'[Store])", 2)