What is DAX?
Data Analysis Expressions (DAX) is a library of functions and operators that can be combined to build formulas and expressions in Power BI, Analysis Services, and Power Pivot in Excel data models.
Calculated Columns
A calculated column creates a new column within your data model that performs each expression based on row context. When we are interested in the expression being evaluated for each individual row, then a calculated column may be created. A calculated column does take up space within the data model. Therefore, we need to be mindful of when a calculated column must be created in our model.
Calculated Measures
A calculated measure is dynamically applied based on the context or filters applied to the visual selected. It does not follow row context but rather gives an aggregated value of the expression being evaluated.
Calculated Tables
Calculated tables are entire tables generated using DAX expressions. These are evaluated as a whole and are useful for creating summary tables, generating relationships with other tables for more complex data models, and creating static lookup tables or aggregations on large datasets.
Now we shall take a quick look at some DAX functions: -
1. Create Single Aggregation Measures
Single aggregation measures in Power BI are used to calculate totals like SUM, AVERAGE, COUNT, MIN, and MAX. For example, a simple measure could calculate total sales using SUM ([Sales Amount]). These measures help in aggregating data over tables and are fundamental in visualizing overall metrics.
To get started, select the required table, and within the table tools ribbon, click on the new measure.
After you click on this, a formula bar appears like this:
After you click on this, a formula bar appears like so:
You can type in your formulas like so:
2. CALCULATE Function
The CALCULATE () function is one of the most powerful in DAX, allowing you to modify filter contexts. It is used to apply custom filters and change the scope of a calculation. For example, you can calculate sales for a specific region by using CALCULATE(SUM([Sales]), [Region] = “West”).
You can use the CALCULATE () function in the following use cases:
- Update/Add a Filter:
The CALCULATE () function allows you to apply or modify filters within the DAX expression itself. These internal filters can be used alongside existing report-level, page-level, or visual-level filters. For example, CALCULATE(SUM([Sales]), [Region] = “West”) adds a filter to show only sales for the “West” region, regardless of other filters applied in the report. - Override/Ignore a Filter:
The CALCULATE () function can also be used to override or ignore existing filters. For instance, if you define a filter for the year 2002 within CALCULATE (), such as CALCULATE(SUM([Sales]), [Year] = 2002), any other year selection made through a slicer or visual will be ignored. The result will still show data for the year 2002, overriding the external filter context. - Remove Filters with ALL ():
When combined with functions like ALL (), ALLSELECTED (), or ALLEXCEPT (), the CALCULATE () function can clear or modify filters. For example, CALCULATE(SUM([Sales]), ALL([Year])) will remove any filter applied on the Year column, returning the total sales across all years, regardless of any other filters present in the report.
3. ALL Function
The ALL () function returns all rows from a table or all values from a column, ignoring any filters that may have been applied to that table or column. In other words, it clears any existing filters and evaluates the entire dataset. When used with the CALCULATE () function, it effectively removes filters from the specified table or column for that calculation.
Example:
CALCULATE(SUM([Revenue]), ALL(Products))
This means that the CALCULATE () function will ignore any filters applied to the Products table and use all rows in the table to calculate the total revenue, regardless of filters in the report or visual.
4. ALL EXCEPT Function
The ALLEXCEPT () function removes filters from all columns in a table except for the specified column(s). This allows you to retain the filter context for specific columns while clearing the others.
Example:
CALCULATE(SUM([Revenue]), ALLEXCEPT (Products, Products [Category]))
In this case, all filters on the Products table will be removed except for the filter applied to the Category column. The calculation will respect the filter for Category but ignore other filters on the Products table.
5. ALL SELECTED Function
The ALLSELECTED () function returns all rows in a table or values in a column, but only within the context of the current selection, meaning it respects the filters applied by visuals or slicers in the report. Unlike ALL (), it doesn’t completely ignore filters but instead works within the user’s selected filters.
Example:
CALCULATE(SUM([Revenue]), ALLSELECTED(Products))
Here, the calculation will consider the selection made by the user in the slicers or visuals, but within that selection, all rows in the Products table will be used for the calculation.
6. Implement Time Intelligence Measures
Time intelligence measures enable you to perform calculations on date ranges, such as year-to-date (YTD), quarter-to-date (QTD), or month-over-month (MoM) comparisons. DAX provides functions like TOTALYTD (), DATESINPERIOD (), and PREVIOUSYEAR () to easily calculate time-based metrics. All time intelligence functions require a Date table in your data model.
7. Use Basic Statistical Functions
Power BI supports various basic statistical functions, such as AVERAGE (), MEDIAN (), STDEV (), and VAR(T). These functions allow you to summarize data and measure statistical properties like distribution and variability, making it easier to interpret key trends and anomalies in datasets.
8. Create Semi-Additive Measures
Semi-additive measures calculate values differently depending on the dimension. For instance, you may want to calculate inventory at the end of a period, not sum it up like regular additive measures. Functions like LASTDATE () and CLOSINGBALANCE () help to create these types of measures in Power BI.
9. Create a Measure by Using Quick Measures
Power BI’s Quick Measures feature simplifies complex calculations without needing to write DAX manually. This option can be found within home menu.
A menu like this opens from which you can easily select the calculations you want to perform:
Users can create common calculations like running totals, percentage of total, or rank by selecting a few parameters from the user interface, making it user-friendly for non-technical users.
