DAX importance and how measures And Calculated Columns are used in DAX .

What is DAX?

Data Analysis Expressions (DAX) can be used to create measures and columns within POWER BI which gives lots of insights of the data. DAX is a collection of functions, operators and constants that can be used in a formula or expression to calculate and return one or more values. It helps you create new information from data already in your model.

Why is DAX important ?

Its easy to create new Power BI desktop file and import some data into it. You can even create valuable insights without using any DAX formulas at all. But in some cases when we need to analyze growth percentage across product categories and for different date ranges, DAX formula provides this capability and many other capabilities as well.

DAX is optimized to work with columnar data models, which makes it efficient when working with larger datasets. It can handle millions of rows of data and return results quickly , when used correctly.

DAX is integral to building data models like creating relationships between tables.

Lets have a brief about functions in DAX :

• Functions are predefined formulas that perform calculations by using specific values called arguments, in a particular order or structure.

  
  

• Arguments can be other functions, another formula, expression, column reference, number, texts,logical values such as TRUE or FALSE , or constants.

  
  

• DAX includes following categories of functions : Date and time, time intelligence, information, logical, mathematical, statistical, Text, Parent/child and other functions.

  
  

• A DAX function always references a complete column or a table. If you want to use only a particular values from a table or column, you can add filters to the formula.

• DAX includes many functions that return a table rather than a value. The table isn't displayed, but is used to provide input to other functions. For example you can retrieve a table and then count the distinct values in it or calculate dynamic sums across filtered tables or columns.

• If you need to customize calculations based on row by row basis, DAX provides functions that let you use row value or related value as a kind of argument to perform calculations based on context.

• DAX includes various time intelligence functions. These functions let you define or select date ranges and perform dynamic calculations based on them. For example, you can compare sums across parallel periods.

Lets get into How measures and calculated columns are used :

Measures and calculated columns both use DAX expressions. You can compute values using calculated columns or measures.

MEASURES:

Measures are computed at the query time. It is stored in the model as a source code , but it is computed only when it is used in the report. Measures can be seen in table but its not visible in the row or as a separate column. Measures can be used to keep data model small.

To create measure we need to select on new measure and then write a DAX function. For example :

Here , I have created a measure using DAX function for total sales. We can see how it is visible.

Measure won't be visible in table as a separate column . In Measures aggregates has to be explicit. This is the measure value in report view.

In measure we won't have aggregates to select as in calculated column. Here in the image we can see when we select a drop down at measure we won't find any aggregates to select. We actually define the aggregation type in measure.

Here in measure by using aggregation i.e. SUMX, we explicit, but in calculated column we don't explicit.

CALCULATED COLUMNS:

Calculated columns are computed based on data that has already been loaded into your data model. When you write the calculated column formula, it automatically applies to the whole table and evaluated individually for each row. Calculated columns are created whenever we need a column to be replaced with new column. Calculated column will be displayed as a separate column with rows in data model view.

To create a calculated column, we use a new column option from table tools on the top of the Power BI page and write a column name, as we can see in the image.

In columns we can implicitly use aggregation type, here in the image we can see when we select the calculated column in report view on the right side we can see a calculated column then click on the drop down where we can select the aggregates such as average, minimum, maximum, count n so on. Firstly when we just select the column name by default it will show sum aggregation.

For Example:

It has a row context and filters can be used in calculated column. Here we are selecting the customer name column to filter the total sales calculated column. As seen in image.

For example: In earlier image we have seen total sales was 11M and now when we filtered with customer name it gets differ.

Calculated columns can be used to build relationships between two tables and the calculated columns can create relationship from one table to another.

By using calculated columns we can do grouping as well and we can use this column as a filter for total sales calculated column. For example:

FEW DIFFERENCES BETWEEN MEASURES AND CALCULATED COLUMNS:

MEASURE	CALCULATED COLUMN
Measures are not precalculated and doesn't have a separate column. It can be used to keep data model small.	Calculated columns are precalculated as rows and shows as a separate column. It will be added in data model.
As it doesn't understand the row context, to aggregate we need to explicilty define, how thevalues are being aggregated.	It is stored as a row context and implicitly apply aggregates on the reports and select the aggregates as per needed.
As it doesn't have row context, it cannot be used as filters and relationships between tables.	As it has row context, it can be used as filters and relationships between multiple tables.
It cannot be used for grouping, as it needs to have row context.	It can be used for grouping which can be used as filters too.