DAX for Data Revolution

In my day to day work I work with DAX, it is a routine for me to create measures and query data for the user requirements, in my personal experience DAX allows to see what you can not simply see, it's a revolution of identifying what do you have in your hands, so I thought of highlighting few points and elaborating more on the usage of DAX. 

First and foremost the important fact is there are three types of calculations that can be defined using DAX in a model and they are as following.

1.       Calculated Columns

2.       Calculated Tables

3.       Measures

Even though we have both MDX and DAX for querying if we are adding a logic to the model it can only be achieved by DAX,

Dax can also be used to add row level security, if we consider Power BI we can create roles and assign those roles with restricted data.

For an example if we have regional data for China, United States and Japan and we need to allow Jhon to access only the data for China we can create a role

[Region] = “China”

And assign this for Jhon, so it creates a row level security and it will only provide the access for the data rows which has China for the column “Region”. Wow you are securing your data.

DAX formulas can only refer three things they are.

1.       Tables- These can be referred with single quote like ‘Sales’

2.       Columns- These can be referred along with a table or even alone when the measure is created within a table like ‘Sales’[Sales Amount] or [Sales Amount]

3.       Measures- These are expressed in square brackets – [Sales performance]

This means they have no understanding of KPI or hierarchies, but using the above you can create a perfect environment for these specific measures, do not worry because all are under hood and you have the power full BI tools such as Power BI. Next in terms of context there are primarily three types of contexts we can use in the data models.

1.       Row

2.       Query

3.       Filter

A row context includes all the values from all columns in the current row. There are two cases, first when there is an active row context. We can evaluate a calculated column or DAX function that iterates over a table.

A query context refers to the subset of data that is implicitly retrieved from a formula, It is retrieved for the all the cells that are referenced.

Filter context is used to modify the context in a formula, it is added to specify filter constraints on the set of values allowed in a column/table, by using arguments to the formula.

Then in DAX “Calculate” is a very powerful and useful function, this evaluates a context modified by a specific filter, here a filter can be

1.       Boolean Expression

2.       Table Expression

Boolean expressions are based on a single column like shown below.

CALCULATE([Sales], Product[Color] = “Red”)

This will filter the data which has the product color red and do calculations on top of it.

Table expressions will return a table and we can use context modifications such as.

CALCULATE([Sales], ALL [ProductColor])

this will fetch all the data ignoring the filters already applied on the Product Color.

Very importantly DAX allows time intelligence, we can do time based calculations such as year to date calculations. It is important to have a table marked as "date" data type in order to use these time intelligence functions.

This will be a brief intro to the world of DAX, simply DAX is a very power full query language which can be used to analyze the data in Excel or Power BI. So using DAX in the right time will help you to discover the hidden truth within your data.