Connect DAX Studio To Power BI And SSAS

by | Power BI

In this tutorial, you’ll learn how to connect DAX Studio to Power BI and to the different variations of the SQL Server Analysis Services.

This demo will go through four processes:

  1. Connecting DAX Studio to Power BI
  2. Connecting DAX Studio to the PowerPivot Model in Excel
  3. Connecting DAX Studio to SQL Server Analysis
  4. Connecting DAX Studio to Power BI Service.

Before proceeding with this, make sure you’ve successfully installed SQL server along with its client tool and database. If you haven’t, go through the installation tutorial first.

1. DAX Studio To Power BI

There are two ways to launch DAX Studio. The first is launching it inside Power BI; while the second is launching it from outside the Power BI program.

To demonstrate how to connect DAX Studio to Power BI, you need to do the second option so open DAX Studio from your computer.

You’ll then see the three Data Source options where you can connect DAX Studio. For this instance, you want to connect to Power BI so you need to choose the PBI / SSDT Model option. Once done, click Connect.

DAX Studio Power BI

You can then see that DAX Studio has connected to Power BI.

DAX Studio Power BI

To check if the connection is working, you can write a simple query such as EVALUATE Customers and then Run it.

You’ll know it’s working since it was able to retrieve the data inside the Power BI model.

2. DAX Studio To The PowerPivot Model In Excel

The next process is to connect DAX studio to the PowerPivot model inside Excel.

Notice in the first process as you open DAX Studio that the PowerPivot option can’t be selected. This is because you can only connect DAX Studio to the PowerPivot model when you’re inside the Excel program. This is applicable if you’re using the portable version of DAX Studio.

Excel will only allow a connection to DAX Studio if it’s launched within the Excel program.

So, open Excel. This is the data used for this example.

DAX Studio Power BI

You can download this file in the DAX Studio For Power BI and SSAS – Beginner to Advanced Course in the Enterprise DNA website.

Then, go to the Add-in tab and select DAX Studio.

DAX Studio Power BI

You’ll then be redirected to DAX Studio. Notice that the option for the PowerPivot connection can now be selected.

DAX Studio Power BI

After you select it and click Connect, DAX Studio will now show all the tables inside the PowerPivot model.

DAX Studio Power BI

Again, to check if the connection is working properly, you can Run a simple query. You can continue to use the EVALUATE Customers code that was used in the first process. If the results show the contents of the Customer’s table, that means that the connection is working.

3. DAX Studio To SQL

The third thing to do is connect DAX Studio to SQL Server Analysis Services (SSAS) and SQL Server Data Tools.

In this example, two models have already been deployed in SSAS.

DAX Studio can’t be launched within the SQL Server Management Studio. So open DAX Studio and connect it from there by choosing the third option, Tabular Server.

DAX Studio Power BI

In the field, write the name of your machine (or computer) and the name of the server separated by a backslash ( \ ). In this case, it’s summer/antriksh. Then, click Connect.

DAX Studio will then contain the two models in the SSAS program.

DAX Studio Power BI

To check if both are working, run a simple query for each model.

With this, you can easily switch between two different models and access the data contained in them.

That’s for connecting to SSAS. Let’s move forward to connecting DAX Studio to the SQL Server Data Tools.

First, launch DAX Studio from your computer and then select the second option PBI / SSDT Model. If you click the drop down menu, you’ll see two options.

DAX Studio Power BI

You can connect to either Power BI or SQL Server Data Tools. The latter is defined by the blue/purple visual studio icon. So, choose Contoso SSAS Integrated.

When you click Connect, you can see that in the Metadata pin, you’re able to view all the tables inside the SQL Server Data Tools program.

Again, to check if the connection is working, you can run a query.

4. DAX Studio To Power BI Service

The last process is to connect DAX Studio to the Power BI Service.

Go to Power BI and then click the Workspace you want DAX Studio to be connected to. Then, click on Settings.

DAX Studio Power BI

Under the Premium option, choose Premium per user and then copy the Workspace Connection string.

DAX Studio Power BI

Launch DAX Studio from your computer and then choose the Tabular Service option. Paste the Workspace Connection string in the field. Then, click Connect.

DAX Studio Power BI

A new window will then appear asking you to sign in. Make sure to log-in to the account you want to use when working with DAX studio and the Power BI Service.

Once done, you can see that you’ve successfully connected to the model in the Power BI Service program.

To check if everything is working properly, run a DAX query. If the results show the contents of the table used in your code, then the connection was successful.

***** Related Links *****
Query Performance And DAX Studio Setup
DAX Query Structure In Power BI – Two Engines & DAX Studio
Materialization Ideas For Data Caches In DAX Studio

Conclusion

This tutorial showed how to connect DAX Studio to four other programs including Power BI and the SQL server analysis services.

DAX Studio is a powerful tool in report development. But when used in conjunction with other tools, more features that increase work efficiency become accessible.

Enterprise DNA Experts

Related Posts

Using the DISTINCT Function Effectively in DAX

DAX Table Functions Deep Dive

Explore an in-depth analysis of DAX table functions in Power BI, comparing SUMMARIZE and ADDCOLUMNS, and understanding INTERSECT and EXCEPT for enhanced data manipulation and analysis.