March 26, 2020

What are DAX function in PowerBI

DAX stands for Data Analysis Expressions; it is Microsoft's language to communicate with data through a range of platforms such as Power BI, PowerPivot, and SSAS tabular models. It is quick and easy to understand while displaying the strength of tabular models and their versatility. In this way, you might compare it to steroid formulae from Excel. Using a DAX function really unleash Power BI capabilities.

What is DAX function in PowerBI?

It is normal to think why a DAX function is so important to learn to work efficiently on Power BI. It is a smooth experience to make reports using the functionalities of data importing, transforming and visualizing in Power BI. To build a satisfactory report with all available data, a user needs to have basic knowledge of the Power BI Desktop. However, if you want to level up your Power BI reports and use advanced calculations, you need DAX.

To get in-Depth knowledge on Power BI you can enroll for a live demo on Power BI online training

Reasons for learning DAX in PowerBI:

Opens up the opportunities:

Learning DAX function as a Power BI user is just like being an Excel user and figuring out how to use formulas. You have been able to organize your tables, add some charts and press the sum / average/... button. Then suddenly you have discovered the VLOOKUP universe, IF function and everything else.Excel still needs formulae to perform certain operations. Learning DAX will open a new Power BI world for you. You can learn DAX and other PowerBI tools from PowerBI online training.

Hassle free:

To reach a point where you are moving a cursor on your screen does not take a lot of practice, because your dashboard does not show you the results you expect. If you know how to use DAX, you can avoid many hassles. A very clear example of that is the card widget's' blank' value. When numerical data is shown in a card, such as' revenue,' it will return' zero' if you set the filters in such a way that there is no revenue to reveal. However, instead of ' null,' a more appropriate way to show' no revenue' would be' 0.' You can construct a calculation by yourself with a very simple DAX expression, adding a' 0' to the formula.

Quicker Dashboard response:

The better you get at DAX, the smarter your dashboard will be able to render. By using DAX, you can create smarter measured columns and/or steps to restrict the data. The dashboard collects and visualizes the data. While some DAX expressions can check the data engine limits, a well-written expression will speed things up.

DAX has usage outside of PowerBI:

You can invest your time in a DAX environment No need to restrict your newly developed ability to a Power BI set. You can also use DAX in other tabular products in Microsoft.

●        Power BI

●        Microsoft Analysis Services

●        Microsoft Power Pivot for Excel

The syntaxof Dax is similar to Excel. Thus, this makes the knowledge also transferable to this good, old, software.

Makes your skills better:

You can use the DAX function in a supportive environment. The ability to learn how to use DAX goes far beyond its reach. DAX is based on a system of numerous nested filter contexts. The performance is important, it changes the way you think about tables and filter data. You may be able to improve the efficiency of some of the Python code you wrote earlier. In other words, learning DAX function will strengthen your way of thinking.

Function of DAX in PowerBI:

DAX function: It is a predefined formula which performs value calculations in the arguments given to it .The arguments in a function must be in a particular order and can be a reference to columns, numbers, text, constants, another formula or function, or a logical value such as TRUE or FALSE. Every function performs a particular operation on the values contained in an argument. For a DAX definition, you can use more than one statement.

Types of DAX function in PowerBI:

The different types of DAX function are as follows

●       Date and time function.

●       Time Intelligence function.

●       Logical function function.

●       Mathematical and trigonometric function

●       Statistical function

●       Text function

●       Parent- child function.

●       Table function

Important DAX function in PowerBI:

SUM():

Summing function adds values in a table column. Subsequent syntax is used for Total (Column Name) function.For example, to produce the total of the sales, follow the steps below.

Step 1: Under Modeling tab, click on New Measure. Form Sales Sum and sort Total formula on equal sign on the right side. Within Amount()form Table1(Sales), as shown below.

Step 2: Use the Visualization Card to test, and add Sales Sum to the canvas. Below we can see that the overall sales figure is just over 81 million.

SUMX():

Suppose you want to measure revenue from sales by multiplying the quantity of orders by unit price. This means computing a column that calculates [unit price]* [order Qty] and then adding the result using SUM(). Using SUMX(),this2-stage method can be achieved easily in a single step. The syntax is as follows.

SUMX (Table, Expression)

CALCULATE()

The CALCULATE function is a very important feature in Power BI because it is used to apply your own filters to data, which can be applied to or even substituted by existing filters.

Following for writing a CALCULATE function is the following syntax.

Take your career to new heights of success with Power BI online training Hyderabad

CALCULATE(<expression>, <filter1>, <filter2>…)

The step-by-step guide below illustrates how we are using Calculate to finance sales SUM where sales are red-colored items.

Phase 1: Under Modeling tab, click on New Measure.

Phase 2: Rename Total Revenue (Red) as metric. This method will create a estimate that will filter out total sales from the Red Sales. Table1 and column Total Sales will be calculated by the CALCULATE function based on product color as red.

Phase 3: Drag Nation, Total Sales Red and Total Sales onto the canvas to test if the latest Total Sales Red measure shows only red sales. As shown below the column Total Sales Red indicates sales of Goods for each nation that are only Red.

Filter():

The filter function operates the same way as CALCULATE(). However, the key difference is that the function of FILTER are not mutable. The data can only be subset. FILTER is an expression that can be used in combination with an existing function such as CALCULATER the SUMX. We used Filter to FILTER the unit price over 300 to measure expensive product prices (over 300).

(<expression>,<filter1>,<filter2>…)

Step 1: Create and name a new measure as Sales from Expensive Goods. Here we will use a Sumx() iterator that will perform a row-by-row order calculation of Qty in Unit Price. The Filter function will limit the calculation to only those items with Unit Price exceeding 300.

Step 2: Drag and drop Product, Total Sales and Sales onto the canvas from expensive goods. Here we can see that sales from expensive goods (product with a unit price higher than 300) belong only to Bikes and Components and generate over 90 percent of total sales.

Conclusion:

By reading this article you may come to a conclusion about the DAX function in PowerBI. You can learn more about Powerbi through PowerBI online Training.