Get Help

Dax@2

Dax@2 reads data from semantic and tabular models — Power BI, MS Fabric, Azure Analysis Services, SQL Server Analysis Services — with DAX or MDX queries. Nothing to install.

Connects to

Semantic models in Power BI and Microsoft Fabric workspaces, semantic models in Azure Analysis Services, and tabular and multidimensional models in SQL Server Analysis Services (SSAS): Power BI Semantic Model, MS Fabric Semantic Model, Azure Analysis Services, SQL Server Analysis Services.

It is technically possible to query a locally open Power BI Desktop file through Dax@2, but PowerBI@2 is much more convenient for that case.

Example

Name
My Power BI Dataset
Technology
Power BI Semantic Model
Provider
Dax@2
Connection string
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%;

Data sources:
- Name: My Power BI Dataset
  Provider: Dax@2
  Technology: PowerBISemanticModel
  Connection string: >
    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.

Connection string

An Analysis Services (ADOMD.NET) connection string — Key=Value pairs separated by semicolons. The keys you will use most:

Key Meaning
Data Source For a Power BI or MS Fabric workspace its XMLA endpoint, powerbi://api.powerbi.com/v1.0/<organization>/<workspace>; for Azure Analysis Services the asazure://… address; for SSAS the server name
Initial Catalog, Catalog The semantic model (dataset, database) to query
User ID, Password Service principal name and secret
Integrated Security SSPI for a local SSAS instance

The full list is in the Analysis Services connection string reference.

A password, or the whole string, can come from an environment variable — %NAME% is replaced with the value of NAME; see How every provider is used.

Workspaces. A Power BI or MS Fabric workspace is reached through its XMLA endpoint, which needs Power BI Premium, Premium per user or a Fabric capacity. The example above signs in as a service principal that has a secret and access to the workspace. Without a service principal you can still automate your tests, but expect the standard Microsoft login prompt — leave both credentials empty:

Name
My Power BI Dataset
Technology
Power BI Semantic Model
Provider
Dax@2
Connection string
Data Source=powerbi://api.powerbi.com/v1.0/your-organization.com/your-workspace-name;
Initial Catalog=Your-Dataset-Name;
User ID=;
Password=;

Data sources:
- Name: My Power BI Dataset
  Provider: Dax@2
  Technology: PowerBISemanticModel
  Connection string: >
    Data Source=powerbi://api.powerbi.com/v1.0/your-organization.com/your-workspace-name;
    Initial Catalog=Your-Dataset-Name;
    User ID=;
    Password=;

Both ways of testing Power BI data — on your machine and in a workspace — are walked through in Test Power BI.

SSAS. A local or on-premises instance, with integrated security:

Name
My Tabular Model
Technology
SQL Server Analysis Services
Provider
Dax@2
Connection string
Provider=MSOLAP;
data source=localhost;
integrated security=SSPI;
Catalog=WideWorldImporters;

Data sources:
- Name: My Tabular Model
  Provider: Dax@2
  Technology: Ssas
  Connection string: >
    Provider=MSOLAP;
    data source=localhost;
    integrated security=SSPI;
    Catalog=WideWorldImporters;

The Provider=MSOLAP; part is accepted and not needed — the driver is ADOMD.NET.

Settings

None beyond Connection string.

Query

Tabular and semantic models do not understand SQL. The statement is one DAX query — knowledge of at least the very basics of DAX is required; multidimensional models in SSAS take MDX instead. GUI for DAX queries shows how to build a query without prior DAX knowledge.

A test comparing the data a DAX query returns (a made-up example without much sense) with expected data 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

Prerequisites

None. The driver ships with CAT; Windows and Linux alike.

Limitations

  • Cannot serve lists of definitions — DAX returns column names in square brackets (Tests[Test name], [Test name]), which CAT does not match to property names.

Serving test and data source definitions

Dax@2 cannot serve lists of definitions. A DAX query returns its column names in square brackets — Tests[Test name] for a model column, [Test name] for a column the query names itself — and CAT matches definition properties by name, brackets included: [Test name] is not Test name.

← Csv@2
Excel@1 →