Power BI: Introduction to DAX

• Rafat
• April 1, 2022

In this article, we will understand everything there is to know about DAX. If you have read my previous article, you must have noticed me defining some DAX functions that were applicable to relationships in Power BI. Since I never previously described DAX, it must have confused you slightly. We will understand what DAX is and its functions in detail.

What is DAX?

The full form of DAX is Data Analysis Expressions. Microsoft built this language to deal with data on a wide range of their platforms. Some of those platforms include MS Power BI, MS Power Pivot for Excel, and others. DAX was designed specifically for the transformation of data models through the use of algorithms and expressions. Basically, DAX is a set of functions, operators, and constants. This entire set helps to build equations that generate values depending on data already in your model.

It is extremely easy and simple to learn and use. If you have used advanced excel before, it is quite comparable to it because excel also offers formulas and expressions which tends to make people’s work easier.

Where can you use DAX formulas?

You can use them in:

• Measures
• Calculated columns
• Calculated tables
• Row-level security

Measures

Measures can be both created and displayed in Power BI Desktop’s Report View or Data View. DAX holds a requirement of defining measures in a table. However, one important thing there to note is the fact that measures do not really belong to the table! So basically, you will be able to place a measure from one table to another one without it losing its functionality. The measures that you create on your own will appear in the Fields list identifiable by a calculator icon. Measures can be given whatever name you desire and included to any new or existing visualization just like any other field.

Calculated Columns

One can create calculated columns by using the new column feature that can be found in Report view. With the help of calculated columns, you will be able to add any new information to an existing table in your model. However, instead of investing so much time into querying and loading certain values into your new column from a data source, you can rather create a DAX formula which will define the column’s values precisely.

Both measures and calculated columns require and employ DAX expressions. The most significant distinction between them is the context of assessment. You can only compute a calculated column at the row level inside the database to which it belongs, whereas you can compute a measure in the context of the cell under consideration either in a report or in a DAX query. You can give your calculated columns any name you want, just like you can give your measure columns any name you want.

Calculated Table

A calculated table is a calculated object that is based on a DAX query or expression and is derived from all or part of other tables in the very same model. They help you to generate new tables using previously entered data in the model. Rather than spending time querying and loading values into your new table’s columns from a data source, you might just save time by defining the table’s contents with a DAX formula.

How To Generate a Calculated Table?

The following are steps to creating a calculated table:

• First, double-check the tabular model and ensure that it has a compatibility level of 1200 or higher. The compatibility level relates to the Analysis Services engine’s release-specific characteristics. It is recommended that you choose the most recent compatibility level that your servers support. Microsoft Analysis Services can be used to generate models with a compatibility level of 1200 or above.
• You must then switch to the data view.
• Next, choose Table > New Calculated Table from the drop-down menu.
• Finally, type a DAX expression or formula.
• Then, give the table a name.
• Now, establish a connection between the model’s tables. (We’ve already gone over this in depth.)
• And at last, use the table as a reference in the calculations of your model.

Row-Level Security

Row-level security (RLS) allows you to limit data access for certain users. It allows you to build a single or multiple reports that are tailored to a certain user’s needs. You can set filters inside roles to restrict data access at the row level. To put it another way, RSL restricts and filters row-level data in a database due to security standards provided by the user. You will be, for example, able to guarantee that your employees only see the data rows that are relevant to their profession.

You can specify RLS for data models brought into Power BI using Power BI Desktop. Before you can configure RLS in Power BI, you must first define roles and rules in Power BI Desktop.

The following are the steps to take in order to achieve this:

• To begin, add data to your Power Bi desktop report.
• Next, go to the Modelling tab.
• Finally, choose Manage Roles.
• Select Create after that.
• Begin by giving the role a name.
• Then, choose the table to which you’d like to apply a DAX calculation/expression. Enter the DAX expression here. Keep in mind that it should produce a true or false result.
• Select the Save option.

Whether or not the relationships between tables are set to single direction or bi-directional, RLS filtering uses single-directional filters by default. However, you can directly activate bi-directional cross-filtering with RLS if you desire to do so. This can be made possible by choosing the relationship and clicking the Apply security filter in both directions option.

Some Advantages Of DAX

The following are some merits of using DAX:

• With the help of DAX expressions, normal tasks can be done easily in less amount of time.
• It helps you in generating new data from existing data in your model.
• DAX promotes efficiency and effectiveness.

The following are the names of some important DAX functions:

• Calculate()
• Related()
• Filter()
• SUM()
• AVERAGE()
• MIN()
• MAX()
• ALL function()