DAX formule u Power Pivot i Power BI

Contents

DAX (Data Analysis Expressions) is a language used to create expressions (formulas) to create reporting dimensions used in PowerPivot tables. The same terms can be used for the tabular model within MS SQL Server Analysis Services . Many DAX formulas have the same syntax as Excel formulas, while some others can work with relational data and perform dynamic data aggregation, filtering, etc.

DAX formule u Power Pivot i Power BI

The introduction may seem intimidating, but DAX expressions are not much different from formulas you created earlier in Excel . We use them to form:

Calculated Columns _
mera (Measures)

The calculated columns are very similar to the columns we add in structured tables. They are important because, in addition to creating report dimensions, we can also use them to create Slicers and Timeline interactive filters.

Measures are formulas that are written directly in the model, and we can use them to create much more complex reporting dimensions. In the 2013 version, the measures were also called Calculated Fields , and since the 2016 version, they have been called Measures again .

In addition to the measures we have made ourselves (explicit measures), there are also implicit measures that are automatically generated by dragging the reported dimensions into the data aggregation fields. For example, dragging the KOL dimension into the Values field aggregation implicitly creates a SUM of KOL measure. As of the 2016 version, there is a possibility that implicit measures can be seen in the model. We achieve this by clicking the Show Implicit Measures button in the Advanced toolbar.

Let’s create two simple DAX expressions! Clicking the Manage button in the Power Pivot toolbar opens the Data Model window, and then we need to position ourselves on the table (some of the tables we previously added to the Model) in which we want to create calculated columns or measures.

If we are adding a calculated column, we need to click under the Add Column label , on the far right side of the table. Then, in the formula entry field, enter the formula by starting it with the “=” sign, and then print the DAX expression. When writing formulas, a mechanism known as Intellisense is launched that automatically offers us column names or DAX formulas that we can use to create expressions, which greatly simplifies the process of writing them. In our example we have selected the table Items we want to create an expression that divides the selling price by the exchange rate EUR: = [PC] / 120

After we have entered the formula, the new column will be automatically filled with the calculated values. It is given the temporary name CalculatedColumn (n is an integer). Since this name is not appropriate, we will change it by right-clicking on the column name and selecting the Rename Column option in the context menu . The column header will change color and we can enter a more appropriate name, e.g. “PC (EUR)”. In this menu, there are several other options for manipulating the calculated columns, which e.g. we can hide, freeze, change their width or order…

Whether we make calculated columns of ilimers, immediately after we make them, we need to determine how to display their content (formatting). The easiest way to do this is to go to the Home toolbar, and in the Formatting section, select one of the formatting options (General, Decimal, Percent, Currency, etc.). The basic formatting that each formula receives after its creation is General , which we can to see when we select a calculated column or measure. Depending on the content of the formula, we further assign them the desired format. For example, a newly created calculated column “PC (EUR)” can be formatted as a decimal number with two decimal places.

We can take measures in two ways. The first way would be to click in the field below the Calculation Area table, in one of the tables where we want to create a measure. Then we state the name of the measure, then the symbol “: =”, and at the end we write the desired DAX expression. For example, if we position ourselves on the Transaction table, we can create a measure that summarizes all quantities sold: AmountQuantity: = AMOUNT ([KOL])

Another way to create measures is by using the New Measure option , which is located in the Measures menu in the Power Pivot toolbar. After launching it, a dialog box will open in the header of which we first enter the name of the table in which we create the measure ( Table Name ), and then the name of the measure ( Measure name ). Finally, in the central part of the window, enter the DAX expression. During input, we can test the formula, and we can also specify the number display format. After we enter the correct formula of the measures we have created, it will appear in the given table of the Data Model. The Measures menu also includes the Manage Measures option , with which we can change previously created measures.

If we now chose the Pivot Table ( Power Pivot ), which we created based on this Data Model, we would see two new reporting dimensions: the added calculated column “PC (EUR)” and the measure called “AmountQuantity”.

Sources:

Rate this post