TAME the the beast that is DAX in Power BI (aka how to learn DAX)

Taming the Beast that is DAX

In this video, Patrick from Guy in a Cube shows how to use DAX functions to solve problems and write measures. He uses an example of calculating the fraction of clients visited in the last three months.

Understanding DAX Functions

• People find DAX challenging because they are not familiar with all the functions available.

• If people understood the functions available, they could solve problems quickly.

• Writing complex DAX can be simplified by understanding the functions available.

Creating a Sample Set

• Before writing a measure, it's important to understand what is being asked for.

• Create a sample set using Excel or write a query that returns a small amount of data.

Using DAX Studio

• Download and use DAX Studio when writing measures.

• Simulate the data that Power BI will use to create visuals by writing queries similar to those used in tables or charts.

Summarized Columns Function

• The Summarized Columns function categorizes all available functions in DAX Studio.

Learning DAX Functions

In this section, the speaker explains how to learn about DAX functions and their capabilities using DAX.guide. They demonstrate how to use the COUNTROWS function to create a measure in Power BI Desktop.

Using DAX.guide

• By entering a parentheses after a function name, you can access more information about that function.

• Clicking on the tooltip opens up a page that goes straight to that DAX.guide.

• The guide provides information about the function, including its parameters and what it returns.

Creating a Measure with COUNTROWS

• The expression represents the measure that will be created in Power BI Desktop.

• To count the number of visits for each client, use COUNTROWS with the client visits table as input.

• Formatting the DAX code helps check syntax errors before running it.

• Creating small datasets allows for easy validation of results.

Filtering by Time Period

In this section, the speaker demonstrates how to filter data by time period using DATESINPERIOD function.

Finding Functions for Time Filtering

• When filtering data by time period, look under daytime folder in functions list.

• Hovering over functions shows inputs required and double-clicking takes you out to documentation.

Using DATESINPERIOD Function

• Use second input of DATESINPERIOD function to provide time period filter.

• Provide calendar table column reference as first input and list of dates as second input.

• Add start date and interval (e.g., month) as additional inputs separated by commas.

Using DAX Studio to Debug and Optimize Queries

In this section, Patrick shows how to use DAX Studio to debug and optimize queries.

Using EOMONTH Function

• Use the EOMONTH function to return the date in a daytime format of the last day of the month.

• To turn a scaler function into a table, use a constructor.

• Replace "today" with EOMONTH and add an additional parameter about where you want to start.

• Use this filter in CALCULATE while simulating the environment in DAX Studio.

Adding Variables

• Add variables using another function that returns the first argument that does not evaluate to a blank value.

• Use variables in summarized columns by knowing their syntax.

Understanding DAX Functions

In this section, the speaker explains how to write DAX functions by understanding them and creating a small dataset for validation.

Learning How to Write DAX

• Understanding the function is the first step in learning how to write DAX.

• The second step is making a small dataset that you can use for validation and iteration.

Writing DAX Expressions

• To write a DAX expression, you need to understand the functions.

• Once you have understood the functions, create a small dataset for validation and iteration.

• Validate your results after each iteration.

Finalizing Your Measure

• Use the expression created earlier to make your measure.

• Copy and paste it into Power BI Desktop's new measure field.

• Ensure that everything is formatted correctly before finalizing your measure.

Conclusion

• The speaker asks viewers how they are tackling and learning DAX, and encourages them to continue the conversation in the comments below.