Introduction to DAX

DAX is mainly used to add calculations to the Power BI Model
You can add 3 types of calculations:

• Calculated Tables
• Calculated Columns
• Measures

It can also be used to define RLS (Row-Level Security)

---

1 - Calculated Tables:

Regarding the calculated tables, DAX is used to whether duplicate or transform an existing model data, create a series data or even produce a new table.
Cons: It increases the model storage size => could prolong the data refresh durations:
Examples: Date tables, Role playing dimensions, what-if analysis, etc...

Note: Calculated table can't connect to external data. You need to use Power Query to do that.

a - Date Table:

Date tables are mostly used for special filters as time intelligence.
For example if you have a model with missing dates and you want to calculate the average per date and the calculation requires the missing dates (ex: holidays), you need to create a date table table with one to many relation to do an accurate calculation.
EX: CALENDAR and CALENDARAUTO

b - Role-playing dimensions:

The Date table can also be defined as role-playing dimension.
This happens when table has two date fields that are related to Date column in Date Table.
(EX: [Sales].OrderedDate and [Sales].DeliveredDate ----> [DateTable].Date
In this case the Date table's Date column is playing the role of Ordered Date and Delivered Date of Sales Table.

Note: Power BI models allow only one active relationship between tables.

Source: Microsoft Learn

So, if you create 2 relations between 2 tables in the Power BI model one of them will be active (solid line) and the other not (dotted line).
But the only way you can use this inactive relation using USERELATIONSHIP DAX function.
Another way is to create 2 date tables.

Source: Microsoft Learn

c - What-if analysis:

What-if analysis is a featured supported in Power BI. It is a parameter that when created automatically adds a calculated table to the model.
They allow selection of filter by value stored in the calculated tables.

---

2 - Calculated Columns:

As we mentioned earlier, DAX can also be used to add calculated columns.

//This is not an actual DAX formula
//But it represents the concept
  foreach(row in table) {
      return value;
  }

It only differs if the connection is Import or Direct Query.
When a calculated column is added to an Import storage mode table, the calculated column is evaluated on refresh which increases the size of the model. But when added to a Direct Query storage mode the calculation is evaluated by the underlying source database upon querying the table.

---

3 - Measures:

Finally, you can write DAX formulas to add measures. It is used to summarize the model data.

For example: Revenue = SUM(Sales[Sales Amount])

Where Sales is the name of the table and Sales Amount is the name of the column that we are calculating.
Unlike calculated columns the returned value of a measure is never stored in the model and the formula is assessed at query time. So, the storage size of the model is affected.