Scribd
Upload
147 views

DAX As A Query Language: Matt Allington

DAX can be used as a query language to extract data from Power BI and Analysis Services tabular models into tables. DAX queries allow summarizing and filtering data efficiently using storage engine functions like SUMMARIZE and FILTER. Queries also help debug filters in measures by showing the underlying table portions. The results of queries can be exported to Excel for further analysis or used to build efficient summary tables to iterate over in measures.

Uploaded by

Moataz Deiab
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
147 views

DAX As A Query Language: Matt Allington

DAX can be used as a query language to extract data from Power BI and Analysis Services tabular models into tables. DAX queries allow summarizing and filtering data efficiently using storage engine functions like SUMMARIZE and FILTER. Queries also help debug filters in measures by showing the underlying table portions. The results of queries can be exported to Excel for further analysis or used to build efficient summary tables to iterate over in measures.

Uploaded by

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

DAX as a Query Language

Matt Allington
Online Feedback
exceleratorbi.com.au
goo.gl/SrvpmJ
About me
• 25 year career at Coca-Cola working in both
Sales and Information Technology
• Now running a Power Pivot consultancy in
Sydney Australia
• Self Service BI Consulting
• Power Pivot/Power BI Training
• Blogger http://xbi.com.au/blog
• Author @ExceleratorBI

• Microsoft MVP https://au.linkedin.com/in/mattallington

http://xbi.com.au

2
Who is in the Audience?
• Who is a SQL Professional or from IT?

• Who is an Excel/Power BI Professional/User?

• What is your DAX Skill?


• Zero, Intermediate, Middle to Pro?
Agenda
• Introduction to DAX as a Query Language
• Use cases for DAX Queries
• Demos
• More demos

Please ask questions


as we go along
Most Common Usage for DAX – As Formulas
• Short for Data Analysis eXpressions
• Great for business/Excel users (Functional Language, similar to Excel)
• Always returns a scalar value, e.g.
• A number
• Text
• Logical TRUE/FALSE

Total Sales = SUMX(Sales,Sales[Qty] * Sales[Unit Price])

Total Sales of Bikes = CALCULATE([Total Sales],


FILTER(ALL(Products[Category]),
Products[Category] = “Bikes”
)
)
DAX as a Query Language
• DAX Queries use the same DAX language
• Mostly functions are in common with standard DAX
• Some special “query only” commands (like EVALUATE).
• Some less common functions like
• SUMMARIZE
• ADDCOLUMNS
• The query sends a request for data to a database
• The result is always a table of data
• Queries return 0, 1 or more rows of data
Use Cases for DAX Queries
• Extracting underlying data into a table
• Detailed
• Summarised

• Learning to write better DAX measures


• Debugging formulas
• Building virtual tables for efficient iteration

• Checking performance of measures (using DAX Studio)


Demo: Intro to DAX Studio and DAX Queries
Key Points: Intro to DAX Studio and DAX Queries
• DAX Studio can connect to different Tabular sources.
• Every query must start with the EVALUATE statement.
• Can use any DAX function that returns a table.
• FILTER()
• VALUES()
• DISTINCT()
• ALL()
• CALCULATETABLE()
• etc
Summarising Data with Queries + Performance

First let’s look at the two Vertipaq engines


Storage Engine and Formula Engine
Storage Engine (SE) Formula Engine (FE)
• Really Fast • Really Smart
• Compresses data on load • Has almost unlimited calculation
capability
• Can retrieve records without
decompressing in the right • Iterates in a row context and
conditions hence it can be slow
• Multi threaded • Single threaded
• Cached • Not cached
SE is the secret to small and FE makes anything possible,
fast workbooks but it can come at a cost
Demo: Summarising Tables
Key Points: Summarising Data
• SUMMARIZE function
• Is a Vertipaq Storage Engine function – very efficient.
• When using more than 1 table, always start with the data table.
• Add extra columns from data or any connected lookup table.
• Can add summarised value columns, but it is inefficient at this task.
• ADDCOLUMNS
• Use in preference to using SUMMARIZE to add value columns.
• Works in a row context, so you must force context transition.
• SUMMARIZECOLUMNS
• Can use this instead of ADDCOLUMNS(SUMMARIZE()).
• No need to specify the starting table.
• Most efficient approach.
Demo: Testing Measure Efficiency

Total Sales Value = SUMX(Sales,[Unit Price] * [Total Sales Qty])


Key Points: Testing Measure Efficiency
• Must use the ROW function to convert a measure to
a table.

• Can use DEFINE function to override (or create)


measures local to the query.

• Clear the cache between tests.


Demo: Debugging filters inside CALCULATE()
Summary: Debugging filters inside CALCULATE()
• DAX queries let you “see” the returned table portion of a
measure/calculated column.

• Can also use New Table in Power BI.

• Sort the table out first, then get back to the measure.
Demo: Extracting data to Excel
Key Point: Extracting Data to Excel
• You must launch DAX Studio from within Excel to be
able to export directly back to Excel.
Same Stores Growth vs LY
Growth in Same Sales vs LY
Same Sales Growth vs LY
Demo

Same Stores Growth vs LY


Key Points: Growth in Same Sales vs LY
• Can use DAX Queries to create efficient summary tables
• Tables will retain lineage to the data model
• Then iterate efficiently over the summary table
Wrap Up and Questions
Power Query Training

Questions? http://xbi.com.au/pqt

Online Feedback

goo.gl/SrvpmJ

DAX Training
http://xbi.com.au/scpbi

• More Information on DAX Queries


https://exceleratorbi.com.au/introduction-dax-
query-language/

You might also like