Dax@2

Dax@2 provider lets you run DAX and MDX queries.

Supported Data Sources

With the Dax@2 provider, you can connect to and query data from:

  • Semantic models in Power BI and Microsoft Fabric workspaces
  • Semantic models in Azure Analysis Services
  • Tabular models in SQL Server Analysis Services (SSAS)
  • Multidimensional models in SQL Server Analysis Services (SSAS)

It is technically possible to query also data from locally open Power BI Desktop file. But for that case we highly recommend PowerBI@1 provider - it is much more convenient.

Power BI Workspace / MS Fabric

Your muse have Power BI Premium or Power BI Premium per user in order to connect to a Power BI semantic model programatically (XMLA endpoint is needed).

Data Sources:
- Name: My Power BI Dataset
  Provider: Dax@2
  ConnectionString:   >
      Data Source=powerbi://api.powerbi.com/v1.0/your-organization.com/your-workspace-name;
      Initial Catalog=Your-Dataset-Name;
      User ID=%YOUR_SERVICE_PRINCIPAL_NAME_ENVIRONMENT_VARIABLE_NAME%;
      Password=%YOUR_SERVICE_PRINCIPAL_SECRET_ENVIRONMENT_VARIABLE_NAME%;

Replace the placeholders (your-organization.com, your-workspace-name and your-dataset-name) with real values. Use environment variables to hide secrets.

If you don’t have service principal with a valid secret and with admin access to your Power BI workspace, don’t worry. You can still automate your test, but expect standard Microsoft login prompt. Use this connection string:

  ConnectionString:   >
      Data Source=powerbi://api.powerbi.com/v1.0/your-organization.com/your-workspace-name;
      Initial Catalog=Your-Dataset-Name;
      User ID=;
      Password=;

We prepared a thorough tutorial about testing Power BI data both on your machine and in Power BI workspace. If the information above is not sufficient for you (it is only a reference), please follow the tutorial.

Connect to SSAS 

Data Sources:
- Name: My Tabular Model
  Provider: Dax@1
  Connection string: Provider=MSOLAP;data source=localhost;integrated security=SSPI;Catalog=WideWorldImporters

Connection string has these parts:

Part Notes
Provider always MSOLAP
Data source IP address or name of your server
Integrated security SSPI (for local connections to SSAS)
Catalog Name of the database you want to run queries against

Example Test

In this test you compare data returned from a DAX query (it is made up example without much sense) with expected data you provided in YAML:

Tests:
- Name: Check zip codes
  First Data Source: MyTabularModel
  First Query: |
    EVALUATE(
    	TOPN(3,VALUES('Customer'[Postal Code]))
    )
    ORDER BY 'Customer'[Postal Code]
  Second data source: yaml
  Second Query: /Expected results
  Expectation: sets match
← Dax@1
Excel@1 →