Articles in this section

See more

Executing DAX Queries against Power BI Semantic Models in QuerySurge (Versions: 14.2+)

Inside Power BI is a data abstraction layer called a Semantic Model which acts as an intermediary between your data sources and your Power BI visualizations. Data in this layer can be queried directly by QuerySurge using our Power BI driver with the powerbiDAX table function. 

This article describes the process for querying data contained within Power BI Semantic Models with QuerySurge. The query language used to access the Semantic Model is Data Analytics Expression (DAX). This article focuses specifically on using DAX to retrieve data, for guidelines on writing DAX expressions refer to the Microsoft documentation here. Additionally, to see what DAX queries are utilized in your Power BI reports please see this article. 

Using DAX Queries in QueryPairs

To execute DAX queries against Power BI Semantic Models in QuerySurge, the powerbiDAX table function can be used. The function’s syntax is as follows:

powerbiDAX(‘workspaceId’, ‘semanticModelId’, ‘DAXQuery’, ‘includeNulls’, ‘impersonatedUsername’)
  • workspaceId (string): ID of the workspace which contains the Semantic Model
  • semanticModelId (Also known as Dataset ID) (string): ID of the Semantic Model which contains the data to extract
  • DAXQuery (string): The DAX query to run execute
  • includeNulls (integer) - Optional field: Defaults to 0. Indicates if null values should be included in the result set
        0 = False
        1 = True
  • impersonatedUsername (string) - Optional field: The user to emulate when querying the Semantic Model. This should be the User Principal Name (UPN). Example: "username@domain.com"

The following is a sample query using the powerbiDAX table function to execute a DAX against a Semantic Model:

SELECT * FROM powerbiDAX(
'e3d3bd4e-3223-4d1b-90e6-5dbb6a3993ba',
'5f974e64-a18f-43d3-9f4f-94a1e4203b31',
'
DEFINE
	VAR __DS0Core = 
		SUMMARIZECOLUMNS(
			''scores''[School Name],
			"SumAverage_Score__SAT_Math_", CALCULATE(SUM(''scores''[Average Score (SAT Math)])),
			"SumAverage_Score__SAT_Reading_", CALCULATE(SUM(''scores''[Average Score (SAT Reading)])),
			"SumAverage_Score__SAT_Writing_", CALCULATE(SUM(''scores''[Average Score (SAT Writing)]))
		)

	VAR __DS0PrimaryWindowed = 
		TOPN(1001, __DS0Core, [SumAverage_Score__SAT_Math_], 0, ''scores''[School Name], 1)

EVALUATE
	__DS0PrimaryWindowed

ORDER BY
	[SumAverage_Score__SAT_Math_] DESC, ''scores''[School Name]
');

Note: DAX Queries can include a mix of single-quote (‘) and double-quote (“) values within the query. Single-quotes must be escaped with an additional quote. For example as seen above the text 'scores' was updated to ''scores''.

The results when running the above query:

Limitations

  • For generating DAX queries from Visualization within Reports, if using a Report that utilizes a Composite Model you will need to use the Dataset ID of the original Upstream dataset, and not the Composite Model dataset itself when using the powerbiDAX SQL table function.
  • Service Principles cannot be used to run DAX queries on semantic models that have RLS assigned to them; only the use of a Master User can be used for DAX queries that have datasets with RLS enabled on them.
  • A maximum of 100,000 rows or 1,000,000 values per query (whichever is hit first) is the limit for data retrieval; as well as a limit of 15MB in total of data is allowed per row. 
  • A limit of 120 requests per minute is enforced.
  • Datasets hosted in Azure Analysis Service or live connections to on-premises Azure Analysis Services models are not supported.
  • Only a single table request is allowed per query.

 

Related articles