Write DAX Queries using DAX Studio Alex Barbeau

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. 3 rd floor by Macy’s starting at 6: 15 § Want More Free Training? o Pass. MN meets the 3 rd 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> [, <name 2>, <expression 2>, …]) § <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>, <group. By_column. Name>[, <group. By_column. Name>]…[, <name>, <expression>]…) § <table> § Any DAX expression that returns a table § A table reference counts § <group. By_column. Name> § 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>, <filter 1>, < filter 2>, …) § <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 <table. Name>[<name>] = <expression> § <table. Name> § 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>) § <table. Name> § 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/ee 634396 § 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