– Advertisement –
This article was published as a part of the Data Science Blogathon.
– Advertisement –
– Advertisement –
If you are new to Power BI Desktop, then this post is for you. You’ll learn the fundamentals of Data Analysis Expressions (DAX) and how to apply them to common math and data analysis tasks in no time. We’ll review some abstract concepts, give you some to practice with, and then have you take a quiz to see how much you’ve learned. It is hoped that readers will end this article with a firm understanding of the most important underpinnings of DAX.
– Advertisement –
Data Analysis Expressions, or DAX for short, are expressions and formulas for analyzing and calculating data. Such expressions are composed of different parts, such as functions, operators, and constants, all combined into a formula and evaluated to provide a result (value or value). Power BI and other BI tools make excellent use of the data at their disposal, and the DAX formulas involved are a big reason.
Take a look at these highlights to have a firmer grasp of the idea.
DAX is what is known as a “functional language”, which means that every code written in it is a function. Conditional statements, nested functions, value references, etc., can all be part of a ready-to-execution DAX expression. DAX formulas can work with two basic data types: numeric and non-numeric or other. Integers, Decimals, Currencies, etc., are all examples of numeric data types. Strings and binary objects form the non-numeric category. Evaluation of DAX expressions starts with the innermost function and proceeds to the outermost function. As a result, it is important to develop the DAX formula. When a DAX formula is executed, it will convert any value of a different data type with the value created for it. The DAX formula will automatically cast the output values to the specified data type. What makes this important?
New Power BI Desktop files can be created and data imported with little effort. You don’t even need to know DAX to create reports that display actionable insights. But what if you need to test growth rates for multiple trading types and time intervals? Or perhaps you’re trying to figure out how to compare your company’s growth rate to the market as a whole; This functionality, along with many others, is provided by DAX formulas. Writing efficient formulas will allow you to make better use of your information. Once you have all the facts, you can start fixing issues related to your company’s bottom line. This is where Power BI shines, and you will find success with the support of DAX.
You are probably already familiar with the ability to create formulas in Microsoft Excel. While familiarity with Excel formulas will help with understanding DAX, the concepts outlined here will allow you to start writing your own DAX formulas and solve practical BI problems quickly.
This lesson will teach us to apply the DAX formulas to Measurements and Calculation columns. It is assumed that you already know the basics of Measure and Calculated columns and how to use Power BI Desktop to import data and add fields to reports.
In most cases, Power BI Desktop automatically generates a measurement for you. Here’s how to create metrics in Power BI Desktop:
Load the sales file in Power BI Desktop.
Open the Sales table in the Fields panel. The SalesAmount field can then be added to the report canvas by checking the box next to it or dragging it there.
When you click on New Column chart, you will get a graphical representation of the sum of all the numbers in the SalesAmount column of the Sales table.
The easiest way to understand DAX is to practice building and using simple formulas on real data. We will be importing the Sales.xls dataset into Power BI Desktop for these exercises.
Parts of a DAX
Syntax, function and context will be the cornerstone on which our DAX education will be built.
Before you start creating your own, let’s look at the syntax of DAX formulas. The syntax of a formula consists of its parts or, more simply, how the calculation is written. This is a basic formula for measurement in DAX:
Types of DAX
In Power BI, DAX can be used to create two different types of expressions and calculations:
Calculated Column Calculated Measure
When you use calculated columns, a new column will be added to your table. A calculated column is similar to any other column except that it must contain at least one function. You can use them to create filtered or sorted columns in your table.
A field with aggregated data (total, ratio, percentage, mean, etc.) is generated by a calculated measurement.
Steps to Create a Calculated Column
1. Turn on Power BI Desktop
2. In the Power BI Desktop left pane, select the Data tab.
3. After this click on New Column button.
4. In the formula bar, enter “column=” and press Enter.
5. The column can be changed to the desired column name.
Steps to Create a Calculated Measure
1. Modeling section of Power BI Desktop.
2. Next, select the menu item “New Solution”.
3. The words “measurement=” will appear in the formula window.
4. You can change “Measure” to another name for a unit of measurement.
5. Next, type the expression for the resulting size to the right of the equal sign.
6. Once you have made a measurement, you can give it a new name by clicking on the calculator icon that appears next to the name of the measurement in the table where it was made.
Knowing the DAX context is vital to mastering DAX syntax with Power BI. In DAX, you can work with row references or filter references.
Row Reference: Using a filtering row as a reference in a DAX expression means “row reference.” The action of the formula on the current row is the primary concern in the row context. Lines of measures usually receive this type of reference.
Filter context: Filter context goes further with a simple emphasis on values. Row context allowed us to choose which rows to process and which others to eliminate. However, when used as a filter, the phrase is limited to particular values within a row. Therefore, the filter context is used together with the row reference to restrict the range of values to which the count is applied. When we use Count, Filter, Related, All, etc we apply filter context.
This concludes our Power BI DAX tutorial. We believe you have learned enough about DAX formulas from this article to get started. Using the columns and tables already present in Power BI, one can write a rational DAX expression to generate more thorough and complex reports.
Discussed the basics of 1.1. DAX is used to handle data modeling
1.2. DAX adds value to the data.
1.3. It is used to visualize the measures of an automated measure – Power BI creates the measures itself. We looked at the parts of the DAX expression. We also discussed the types of DAX measures. We have seen the detailed steps to create a Calculated Column. We discussed the detailed procedure for creating the calculated measures.
Leave your comment with any thoughts you have.
The media shown in this article is not owned by Analytics Vidya and is used at the sole discretion of the author.
– Advertisement –