Writing DAX Queries

We discuss the tools available to you when writing DAX queries, including Power BI, SQL Server Management Studio and DAX Studio

In August of 2022, DAX Studio v3 was released. We talk about that here.

In this lecture we show what a complete DAX query looks like by building a matrix in Power BI and then having DAX Studio capture queries Power BI sends to Analysis Services. We focus on one of these queries to see what a complete query looks like.

We cover the EVALUATE statement in depth. This is part 1 of 2 parts.

We cover the EVALUATE statement in depth. This is part 2 of 2 parts.

In this lecture we cover the CALCULATETABLE function and how, in many cases, it should be used instead of FILTER. Note, this is basically the same lecture contained in our CALCULATE Function course on the CALCULATETABLE function. So, if this sounds familiar to you, this is why.

In this lecture we demonstrate the use of SELECTCOLUMNS and ADDCOLUMNS

Here we discuss the SUMMARIZE function which is generally used to group data. It can also be used to aggregate data but shouldn't be, as we will discuss.

This function is like SUMMARIZE v2 with one major limitation

In this lecture we discuss the ALL function and the dual purpose it serves

We demonstrate a real-world scenario where SUMMARIZE doesn't quite satisfy the requirements but CROSSJOIN does

The TOPN and GENERATE functions are actually quite simple functions that can solve very complex requirements, as we'll see in this lecture.

In this lecture we discuss the UNION function and how it can be useful

In this lecture we show how to use the INTERSECT function to determine repeat customers and we demonstrate the importance of being able to use DAX as a general purpose language.

In this lecture we discuss the EXCEPT function and how it can be used. Specifically we use it to calculate new customer counts for each date of sale in the Contoso database.

A very useful operator for creating a calculated column in a DAX query in DAX Studio.

We've thoroughly covered how to create a DAX query and the most common and useful table functions. We're going to wrap things up by discussing the importance of data lineage when using these table functions and how TREATAS can be used when you need to establish or change the data lineage of a piece of data.

In this lecture we demonstrate many of the common T-SQL queries you might write as a SQL developer and then show how to write the same queries using DAX.

Part 2 of our lecture on demonstrating many of the common T-SQL queries you might write as a SQL developer and then showing how to write the same queries using DAX.

Part 3 of our lecture on demonstrating many of the common T-SQL queries you might write as a SQL developer and then showing how to write the same queries using DAX.

In this lecture we talk about how to export query results and tables to CSV and SQL Server

Did you know that you can have DAX Studio prompt you for a value to be inserted into your DAX query? We show you how in this lecture.

A discussion of the new INFO functions added to Power BI

The first batch of exercises

The second batch of exercises. Note: For exercise 8, you will notice that incorrect results are generated in the lecture, although I didn't realize it at the time. I have made the fix and the downloadable Queries.dax file now contains the correct query, for exercise 8 and generates the correct results.

The third batch of exercises