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.
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 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.
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.
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.
The following are steps to creating a calculated table:
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:
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.
The following are some merits of using DAX:
The following are the names of some important DAX functions: