---
title: DAX_Studio_Eng
tags: 
author: [Eiki Sui](https://convert.docswell.com/user/marshal_dabao)
site: [Docswell](https://www.docswell.com/)
thumbnail: https://bcdn.docswell.com/page/GJ5G3KW9J4.jpg?width=480
description: Explanation of DAX Studio used to measure the performance of tabular models in Power BI and Analysis Services
published: July 08, 24
canonical: https://convert.docswell.com/s/marshal_dabao/KRXLV4-2024-07-08-231029
---
# Page. 1

![Page Image](https://bcdn.docswell.com/page/GJ5G3KW9J4.jpg)

Introducing DAX Studio
Last update: Jan-2024
Fabric CAT: Eiki Sui


# Page. 2

![Page Image](https://bcdn.docswell.com/page/LE3R6Z4275.jpg)

DAX Studio
External tools that connect to Power BI or
an Analysis Service instance of Excel to
obtain and analyze various metadata
information


# Page. 3

![Page Image](https://bcdn.docswell.com/page/8EDYVRQ6EG.jpg)

DAX Studio
Developer
Darren Gosbell
SENIOR PROGRAM MANAGER
Joined MS in June-2022, Power BI CAT in Australia; developed DAX
Studio since 2011 to date
As of 7/1/2022, DAX Studio has been downloaded more than
134,000 times in its existing version
https://daxstudio.org/
SLQBI
Marco Russo &amp; Alberto Ferrari
Official site (English)
https://daxstudio.org/doc
umentation/
How to use (Japanese -Qiita)
https://bit.ly/3bGtDoK
Pioneer in Microsoft BI technology.
They run SQLBI, which provides technical know-how on DAX /
Data Model, code checking and UI design support for DAX Studio
The Definitive Guide to DAX
Is the Bible for all DAX students to refer to


# Page. 4

![Page Image](https://bcdn.docswell.com/page/V7P6VWGZJ8.jpg)

External Tools
Concept of External Tools
• Mostly done by community work
• Mostly open source
Tech-tech Blog (Japanese)
https://marshal115.hatenablo
g.com/entry/2022/05/27/181
736
External tools (Official)
https://docs.microsoft.com/
power-bi/transformmodel/desktop-externaltools
• Main purpose is to improve efficiency in Power BI
• DAX Studio is one of the most important
external tools that have existed for a long time
(before the advent of Power BI)
• Analyze in Excel, Bravo for Power BI、Tabular
Editor 2, Tabular Editor 3, etc. are well known


# Page. 5

![Page Image](https://bcdn.docswell.com/page/2JV3Z86MEQ.jpg)

Using DAX Studio
Launch DAX Studio


# Page. 6

![Page Image](https://bcdn.docswell.com/page/5EG345NX7L.jpg)

Connection
options
Connection Tools
1. Power BI Desktop
2. Excel Power Pivot
◆ Since it is usually used with
Power BI, the following
discussion will be basically
based on Power BI.
3. SSDT※1
4. Analysis Service
5. Azure Analysis Service
6. Power BI XLMA Endpoint※2
※1 Development tool to design and deploy SQL Server content types
※2 Power BI Premium or PPU license required
Details of connection options (DAX Studio Official Doc)
https://daxstudio.org/tutorials/getting-connected/


# Page. 7

![Page Image](https://bcdn.docswell.com/page/4JQ5GZQ57P.jpg)

Installing &amp; Starting DAX Studio
1. Install DAX Studio
https://daxstudio.org/
2. Launch of DAX Studio
Launch from Power BI
Launch from Excel
Launch from DAX Studio


# Page. 8

![Page Image](https://bcdn.docswell.com/page/K74953NVE1.jpg)

Launch from
Power BI
Launch from Power BI
1. Open the target Power BI Desktop
2. External Tools &gt; DAX Studio
◆ When DAX Studio is
installed, it appears as an
external tool for Power BI
Desktop
◆ Please use the latest
versions of DAX Studio and
Power BI Desktop when
possible
※ Assuming data model is available


# Page. 9

![Page Image](https://bcdn.docswell.com/page/LJ1KW1547G.jpg)

Launch from
Excel
Launch from Excel
1. Launch Excel (Power Pivot Model required)
2. Add-Ins &gt; DAX Studio
◆ When connecting DAX
Studio to an Excel data
model, always start Excel
first


# Page. 10

![Page Image](https://bcdn.docswell.com/page/GJWD685Z72.jpg)

Launch from
DAX Studio
◆ When launching from DAX
Studio, perform a search
from the search window
◆ Make a connection to the
Analysis Service instance of
Power BI Desktop
◆ Since Excel has its own
instance, connection is not
possible when launching
from DAX Studio. DAX
Studio must be launched
from an add-in in Excel
(because the network port
is different)
Launch from DAX Studio
1. Launch DAX Studio


# Page. 11

![Page Image](https://bcdn.docswell.com/page/4EZGY8NLJ3.jpg)

Key practical features
with DAX Studio
Functions that cannot or are
difficult to achieve with Power BI
Desktop


# Page. 12

![Page Image](https://bcdn.docswell.com/page/Y76YDP8MEV.jpg)

Key features
offered by
DAX Studio
Metadata Information
Measuring Query Performance
Query Editor Feature
Data Export Feature
Features (Official - English)
https://daxstudio.org/docum
entation/features/
Query Builder
DAX Formatting Feature


# Page. 13

![Page Image](https://bcdn.docswell.com/page/G75G3K9Q74.jpg)

DAX Studio features
Metadata Information
Official site (English): https://daxstudio.org/documentation/features/model-metrics/


# Page. 14

![Page Image](https://bcdn.docswell.com/page/9J2YZW2WJR.jpg)

DAX Studio features
Measuring Query Performance
Optimizing DAX（Japanese）
https://marshal115.hatenablog.com/entry/2022/02/24/181416


# Page. 15

![Page Image](https://bcdn.docswell.com/page/DEYGRLY9EM.jpg)

DAX Studio features
Query Editor Feature
Official site (English)
https://daxstudio.org/tutorials/writing-dax-queries/


# Page. 16

![Page Image](https://bcdn.docswell.com/page/VJNGD4PD78.jpg)

DAX Studio features
Data Export Feature
◆ General export
◆ Bulk export
Blog in Japanese: https://marshal115.hatenablog.com/entry/2020/06/06/182918


# Page. 17

![Page Image](https://bcdn.docswell.com/page/YE9WGQ38E3.jpg)

DAX Studio features
Query Builder
◆ Query Builder allows DAX
queries to be generated with a
simple Drag &amp; Drop
◆ As a use case, output raw data
with generated query for reuse
Official site (English):
https://daxstudio.org/documentation/
features/query-builder/


# Page. 18

![Page Image](https://bcdn.docswell.com/page/GE8NVGMZ7D.jpg)

DAX Studio features
DAX Formatting Feature
Note: DAX Studio will send request to
DAX Formatter so network is required
https://www.daxformatter.com/


# Page. 19

![Page Image](https://bcdn.docswell.com/page/LELP5G317R.jpg)

DAX Studio
Notes
DAX Studio is not for beginners, but a tool
for intermediate to developers. Beginners
can also use it if they feel comfortable


# Page. 20

![Page Image](https://bcdn.docswell.com/page/4JM43QD57W.jpg)

Practical examples
Practical use cases


# Page. 21

![Page Image](https://bcdn.docswell.com/page/PJRWQ84Z79.jpg)

Example of use: Displaying metadata information
Confirmation of overall model
Consider optimization needs by understanding model metadata information
Total Size is usually
larger than the PBIX
file
E.g.: Is the number of tables = 7 and the number of
columns = 61 the optimal number?


# Page. 22

![Page Image](https://bcdn.docswell.com/page/PEXP58217X.jpg)

Example of use: Displaying metadata information
Check the details of the model
Breakdown from overview (tables) to details (columns)
Are these columns with high cardinality necessary?
Wouldn&#039;t deleting them reduce the DB size?


# Page. 23

![Page Image](https://bcdn.docswell.com/page/3EKD6KMMJD.jpg)

Example of use: Measuring query performance
Query optimization
40x performance improvement


# Page. 24

![Page Image](https://bcdn.docswell.com/page/L73R6ZY2J5.jpg)

Example of use: Measuring query performance
Check the specs of your PC
Tech-tech blog (Japanese)
Reference
※ Note
Be sure to launch DAX Studio
from the pbix file containing the
data model.


# Page. 25

![Page Image](https://bcdn.docswell.com/page/87DYVR567G.jpg)

Example of use: Measuring query performance
Check the specs of your PC
Check to see if your PC is fast enough to use Power BI
DEFINE
var _a = SELECTCOLUMNS(CALENDAR(1, 10000), “a”, [Date])
var _b = SELECTCOLUMNS(CALENDAR(1, 10000), “b”, [Date])
var _joined = CROSSJOIN( _a, _b )
var _result = COUNTROWS( _joined )
EVALUATE
ROW(&quot;Result&quot;, _result )
※ Tip
◆ Keep other applications down when testing
◆ Test with the PC connected to a power source


# Page. 26

![Page Image](https://bcdn.docswell.com/page/VJP6VWDZE8.jpg)

Example of use: Query Editor function
Practicing DAX
Learn how to write DAX with DAX Studio.
① Start with EVALUATE
② Write DAX
③ F5 to
refresh
Official site (English)
https://daxstudio.org/tutorials/writing-dax-queries/


# Page. 27

![Page Image](https://bcdn.docswell.com/page/2EV3Z81M7Q.jpg)

Example of use: Query Editor function
Practicing DAX
Easily check DAX queries in DAX Studio instead of Power BI
【Explanation of DAX Queries】
1. Add two columns for
Product[Category]: [All Sales]
and [FY2020 Sales]
2. Calculate [% of FY2020 Sales]
(=[FY2020 Sales]÷[All Sales])
with the results of the two
added columns
※All DAX queries built in DAX Studio
are table-based results.


# Page. 28

![Page Image](https://bcdn.docswell.com/page/57G3452XJL.jpg)

Example of use: Query Builder
DAX beginner-friendly
【 Advantages of the Query Builder 】
◆ Easy to build DAX queries even for
beginners
◆ Can study automatically constructed
queries
◆ Can export data in any form you like
(* be sure to consider Power BI
governance before exporting data)
◆ Can convert these queries to Excel
(see blog below)
Tech-tech blog（Japanese）
Application of DAX Query


# Page. 29

![Page Image](https://bcdn.docswell.com/page/4EQ5GZP5JP.jpg)

Example of use: Displaying metadata information
Export / import of VPAX file
1. DAX Studio &gt; Advanced &gt; Export Metrics &gt; Save
2. Launch an empty DAX Studio &gt; Advanced &gt; Import Metrics &gt;
Select .VPAX to import (see below)
3. Used to check models that do not contain original data (metadata only)


# Page. 30

![Page Image](https://bcdn.docswell.com/page/KJ4953YV71.jpg)

Exporting Data
There is no export limit, but it depends on
the specifications of the local PC. I’ve
exported 600 million lines (6 GB) of data
in success
by Darren


# Page. 31

![Page Image](https://bcdn.docswell.com/page/LE1KW164JG.jpg)

Example of use: Data export function
Batch export to CSV files


# Page. 32

![Page Image](https://bcdn.docswell.com/page/GEWD68WZJ2.jpg)

Example of use: Data export function
Batch export to CSV files


# Page. 33

![Page Image](https://bcdn.docswell.com/page/47ZGY85LE3.jpg)

Example of use: Data export function
Batch output to Database
Bulk output from DAX Studio to SQL servers with write permission


# Page. 34

![Page Image](https://bcdn.docswell.com/page/YJ6YDP9M7V.jpg)

Example of use: Data export function
Batch output to Database


# Page. 35

![Page Image](https://bcdn.docswell.com/page/GJ5G3KNQJ4.jpg)

Example of use: Data export function
Batch output to Database


# Page. 36

![Page Image](https://bcdn.docswell.com/page/9E2YZW5WER.jpg)

Example of use: Data export function
Batch output to Database


# Page. 37

![Page Image](https://bcdn.docswell.com/page/D7YGRLK97M.jpg)

Example of use: Data export function
Batch output to Database


# Page. 38

![Page Image](https://bcdn.docswell.com/page/VENGD4RDJ8.jpg)

Example of use: Data export function
Connect to the database
Connecting with Power BI Desktop


# Page. 39

![Page Image](https://bcdn.docswell.com/page/Y79WGQM873.jpg)

Example of use: Data export function
Connect to the database


# Page. 40

![Page Image](https://bcdn.docswell.com/page/G78NVGLZJD.jpg)

Example of use: Data export function
Extract M queries from DAX Studio (DMV)
Connect to Power BI Desktop / Power BI Service and fill in the following code
※ Note
1. DMV = Dynamic
Management View
Queries that return
information about model
objects, server operations,
and server health
2. For Power BI Service,
Premium capacity or
PPU required
Reference:
Chris Webb&#039;s blog


# Page. 41

![Page Image](https://bcdn.docswell.com/page/L7LP5GL1JR.jpg)

Example of use: Data export function
Extract DAX queries from DAX Studio (DMV)
SELECT
MEASUREGROUP_NAME,
MEASURE_NAME,
EXPRESSION
FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE MEASURE_AGGREGATOR = 0
ORDER BY MEASUREGROUP_NAME
※ Note
1. DMV = Dynamic
Management View
Queries that return
information about model
objects, server operations,
and server health


# Page. 42

![Page Image](https://bcdn.docswell.com/page/4EM43QM5JW.jpg)

Example of use: Other
Obtaining the server name (Port)
Launch DAX Studio with Power BI Desktop or other software.
2. Click on the icon in the lower right corner of the editor (see below)
1.
3.
Launch Notepad, etc., and make sure that the name has been acquired
4.
Put the above into Excel or SSMS servers to enable connection.
Excel: “Data” tab &gt; Get Data &gt; From Database &gt; From Analysis Services &gt; Server Name


# Page. 43

![Page Image](https://bcdn.docswell.com/page/PERWQ8VZJ9.jpg)

Example of use: Other
Analyze in Excel feature
“Analyze in Excel&quot; functionality available from DAX Studio
Tech-tech blog (Japanese)
Analyze in Excel ①
https://marshal115.hatenablog.com/entry/2021/06/13/171554
Analyze in Excel ②
https://marshal115.hatenablog.com/entry/2021/10/16/164259


# Page. 44

![Page Image](https://bcdn.docswell.com/page/P7XP58Z1JX.jpg)

Main Resources
DAX Studio official site (English)
https://daxstudio.org/document
ation/
Tech-tech blog (Japanese)
https://marshal115.hatenablo
g.com/
Others (Qitta -Japanese)
https://bit.ly/3bGtDoK


