KEMBAR78
DAX Studio Query Writing Guide | PDF | Data Management | Computing
Scribd
Upload
719 views22 pages

DAX Studio Query Writing Guide

The document is an agenda for a presentation on writing DAX queries using DAX Studio. It includes sections on why query with DAX, an introduction to DAX Studio, and examples of DAX query functions like EVALUATE, ROW, SUMMARIZE, ADDCOLUMNS, CALCULATETABLE and DEFINE MEASURE. It encourages attendees to ask questions and provides resources for further learning about DAX and DAX Studio.

Uploaded by

Dwiki Aulia Rahman
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
719 views22 pages

DAX Studio Query Writing Guide

The document is an agenda for a presentation on writing DAX queries using DAX Studio. It includes sections on why query with DAX, an introduction to DAX Studio, and examples of DAX query functions like EVALUATE, ROW, SUMMARIZE, ADDCOLUMNS, CALCULATETABLE and DEFINE MEASURE. It encourages attendees to ask questions and provides resources for further learning about DAX and DAX Studio.

Uploaded by

Dwiki Aulia Rahman
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 22

Write DAX Queries using DAX Studio

Alex Barbeau
alex.barbeau@gnetgroup.com
Let’s get started

 Thank you Sponsors!


o Please visit the sponsors during the vendor break
from 2:45 – 3:15 and enter their end-of-day raffles

 Event After Party


o Dave and Buster’s in Southdale Center. 3rd floor by
Macy’s starting at 6:15

 Want More Free Training?


o PassMN meets the 3rd Tuesday of every month.
https://mnssug.org/

2 | 10/10/2015 Write DAX Queries using DAX Studio


Agenda

 Why Query with DAX?


 DAX Studio
 Demos!
 Questions?

3 | 10/10/2015 Write DAX Queries using DAX Studio


DAX is a Query Language??

 Indeed it is!
 Measures are just one (very important) piece
 It’s tables all the way down
 Vectors and Matrices are a good theoretical start
 A query returns a table
 Queries are natively ‘Grouped By’

4 | 10/10/2015 Write DAX Queries using DAX Studio


Why Query Power BI/Tabular

 You need a data source for:


 Datazen
 SSRS
 SQL Server Reporting Services
 External Reporting Tool
 Your own self-service BI from existing models
 Need to extract data from Power BI Desktop
 DAX Development
 Great way expand understanding of DAX

5 | 10/10/2015 Write DAX Queries using DAX Studio


Note on Terminology

 By Tabular I am referring to the framework


behind the following software:
 SSAS Tabular 2012-2016
 Power Pivot in Excel 2010-2016
 Power BI Desktop
 Query structure is the same in all
 Queries will use standard ‘Table’[Column]
references and [Measure] references

6 | 10/10/2015 Write DAX Queries using DAX Studio


Please ask Questions

 Odds are others have similar questions


 A full response may be delayed till a later
slide
 Some queries may be deliberately poorly
written to demonstrate concepts and syntax
 Basic familiarity with DAX assumed here-on
 Columnar storage
 CALCULATE(), VALUES(), FILTER()

7 | 10/10/2015 Write DAX Queries using DAX Studio


DAX Development

 Write queries and develop measures against


a new or existing model
 Power Pivot
 Visual Studio
 SSMS
 SQL Server Management Studio
 Excel (2013+)
 Edit DAX from a table
 Bit of an involved process
 DAX Studio

8 | 10/10/2015 Write DAX Queries using DAX Studio


DAX Studio

 It Queries!
 It Formats!
 It Traces!

9 | 10/10/2015 Write DAX Queries using DAX Studio


DAX Studio

 Open Source tool for querying Tabular


models
 http://daxstudio.codeplex.com/
 Developed (principally) by
 Darren Gosbell
 Marco Russo
 Query Excel, Tabular, and Power BI Desktop
 Let the team know what you think!

10 | 10/10/2015 Write DAX Queries using DAX Studio


Today’s Model

 Adventure Works Internet Sales

11 | 10/10/2015 Write DAX Queries using DAX Studio


EVALUATE

 Think of this like SELECT


 Does nothing without an expression to
 Wait for it
 EVALUATE

12 | 10/10/2015 Write DAX Queries using DAX Studio


ROW()

 Return a row of values


 ROW( <name>, <expression> [, <name2>,
<expression2>,…])
 <name> is any “text within quotes”
 <expression> is any DAX expression that returns
a scalar

13 | 10/10/2015 Write DAX Queries using DAX Studio


SUMMARIZE()

 Build tables as you need them


 SUMMARIZE(<table>, <groupBy_columnName>[,
<groupBy_columnName>]…[, <name>,
<expression>]…)
 <table>
 Any DAX expression that returns a table
 A table reference counts
 <groupBy_columnName>
 Any column in any table
 A column in a related table is a good start
 <name>, <expression>
 Just like ROW(), but now for the context defined above

14 | 10/10/2015 Write DAX Queries using DAX Studio


ADDCOLUMNS()

 Add values to a given table


 ADDCOLUMNS(<table>, <name>,
<expression>[, <name>, <expression>]…)
 <table>
 Any DAX expression that returns a table
 <name>
 Same as in ROW()
 <expression>
 Any DAX expression that returns a scalar for a given row in the <table>

 SUMMARIZE() returns a table…

15 | 10/10/2015 Write DAX Queries using DAX Studio


Best Practice Sidebar

 Best Practice to wrap ADDCOLUMNS()


around SUMMARIZE()
 Reduces unnecessary calculations
 Finer control over what is returned
 Generally faster
 Model dependent of course
 Exception is ROLLUP() commands, out of
scope for this session

16 | 10/10/2015 Write DAX Queries using DAX Studio


CALCULATETABLE()

 Efficient (generally) way to filter a query


 CALCULATETABLE(<expression>,<filter1>,<
filter2>,…)
 <expression>
 A DAX expression that returns a table
 <filter>
 Boolean expression or a table expression that
defines a filter

17 | 10/10/2015 Write DAX Queries using DAX Studio


DEFINE MEASURE

 Re-usable measures just for your query


 DEFINE MEASURE <tableName>[<name>] =
<expression>
 <tableName>
 An existing table in the data model
 <name>
 Same as prior slides
 <expression>
 A DAX expression that returns a scalar

18 | 10/10/2015 Write DAX Queries using DAX Studio


SUMX()

 Turning your query into a measure


 SUMX(<table>, <expression>)
 <tableName>
 An existing table in the data model
 <name>
 Same as prior slides
 <expression>
 A DAX expression that returns a scalar

19 | 10/10/2015 Write DAX Queries using DAX Studio


Resources
 DAX Studio
 http://daxstudio.codeplex.com/
 SQLBI.com
 Marco and Alberto wrote the book(s) on DAX
 DAX function Reference
 https://msdn.microsoft.com/en-us/ee634396
 Community
 MSDN Forums
 https://social.msdn.microsoft.com/Forums/sqlserver/en-
US/home?forum=sqlkjpowerpivotforexcel
 Model
 https://awinternetsalestabularmodel.codeplex.com/

20 | 10/10/2015 Write DAX Queries using DAX Studio


Questions?

 Comments?
 Points of Rebuttal?

21 | 10/10/2015 Write DAX Queries using DAX Studio


SQL Saturday Evaluations

 Remember to fill out your online evaluations


for the event and any sessions you have
attended. They will be online until 10/17/15.

http://www.sqlsaturday.com/453/eventeval.aspx
http://www.sqlsaturday.com/453/sessions/sessione
valuation.aspx

22 | 10/10/2015 Write DAX Queries using DAX Studio

You might also like