Hi folks, I hope you all are doing well. In our daily work, we all use calculations to get the result of anything. And, also we all work with data ( Any kind ). Without making this article boring let’s come to the gist ( “essence” or “the main point.” ) of the topic. The first question that comes to our mind is “what is DAX” and “What is Power-Bi ?”. So, the answer is DAX is a language which is used to do calculations on data and Power-Bi is a tool that helps you to create report and dashboard based on data. To generate or measure new values or meaningful data we use DAX.
Let’s go into deep of it. Before we go ahead- Let’s check the below pointer first-
What is Power-BI?
Power-Bi is a Microsoft tool that gives us a platform to easily create reports and dashboards using data. Data can come from any source like database, csv, xls file and so on. Power-Bi provides multiple functionalities to manipulate the data and visuals also. It comes with two packages as Power-Bi service and Power-Bi desktop. Power is one of the most successful tools of Microsoft for visualizing the data in the form reports or dashboard. Below is the screen-shot of Power-Bi tool.
This is a widely used visualization tool which is used entirely at every section and domain of the technology. This tool not only helps to cerate better dashboards and reports but also help to ensure better insights for the requirements. When we talk about insights these are the solution which we can drive from this POWER BI visualization tool for a large number of data. The Data varies from the hundred to millions. These reports are easily understandable and user-friendly and one can quickly derive, meaningful information out of it.
What is DAX?
DAX is “Data Analysis Expression”. I guess you all understand the meaning of DAX by just reading it. DAX is used to analyze or generate new data based on existing data. DAX is a combination of functions, constants, and operators that are used in an expression to calculate the desired values or results.
DAX plays a major role in power-bi for manipulating the data as per our need at any time with simple functions. DAX is a functional language that contains a fully executed code inside the function. DAX is designed to work with tables, that’s why it has only two data-type Numeric or other. Numeric data-type refers to integers, decimals and currency. Other refers to string and binary objects.
This language is used only in POWER BI setup which helps to derive the better meaning of the raw data dn to convert them to meaningful information. Likewise, we work on any programming language to simplify data similarly DAX is used in POWER BI to access the raw and large number of information into useful and meaningful pieces of information. DAX is not set with day 1 launch of POWER BI it was launched in the improvements of POWER BI and was introduced via their blog section. And then came into existence for the users to work and get the useful outcomes.
DAX provides extra and important ability to power-bi. Let’s take an example of sample data of employee salary and company profit data based on that data we need to calculate the gross annual profit or year-to-year profit. But without using DAX it is quite hard or impossible to calculate that value so, in this case, DAX is very helpful for us because it provides the function to perform that operation.
DAX is very easy to use and learn. DAX provides lots of functions like SUM(), Cal() etc to manipulate the data. It is very easy to use once the user understands their functions and constants. With this, one must use DAX function as this is highly in demand and used worldwide. Once you learn DAX it becomes easy to understand and reliable to use.
Let’s Start with DAX
To understand the DAX, first, we need to understand the three concepts of it: Syntax, functions and context.
A Syntax is the structural representation of statements. It is the representation of formula as shown below.
The above expression contains SUM DAX function that takes the Sales Amount column from Sales table and returns the sum of Sales Amount values as Total Sales.
NOTE: If the table name contains any kind of special character or spaces then you’ll need to define the table name within single quotation marks.
DAX Calculation types
There are two types of calculations done by using DAX as below:
- Calculated columns
- Calculated measures
In this method one new column will be added in the table by clicking the Modeling tabs after that we need to select the new column option, it will show the calculation area. In the calculation area, we need to add the formula we want. The result will be reflected in the table as well as filed pane also. Below is the screen-shot of the same.
In this section, we will perform the same operation as for the calculated column but we need to select the new measure option in the modeling tab. Also, the result will not affect the table but affect the field pane. Below is the screen-shot of the same.
There are six types of DAX function to reshape the data. Below is the list of functions that we will use to calculate the new values based existing values.
- Aggregation functions
- Counting functions
- Logical functions
- Information functions
- Text functions
- Date functions
We can use the above functions in anytime to evaluate the desired values from the existing values. For more info, you can check the link below.
In this article, we have understood the basis of DAX in Power BI along with Syntax and functions. I will come up with more details into it in my next blog so keep following our blogs and for any queries, please feel free to share your views via the comment section below.
With this, if you have any doubt in writing DAX for the first time – kindly ping me here so I can connect with you and assist you.