Power BI variables with efficiency and debugging

Power BI Variables are one of the significant components while writing a DAX function in Power BI. In DAX function, the same expression need not be written again and again within the current context; instead, variables can be used to store the results of the expression. They can be used any number of times within the current scope. The same variable can also be used elsewhere in the dashboard, which will not change the existing result within a calculated column or a measure. 

Using Variables with DAX has a lot of advantages. 

1. Improved Efficiency
2. Improved readability
3. Helps in step-by-step debugging
4. Reduces the complexity of the DAX 

Power BI variables usage

Let’s see a simple example using variables. Here we are calculating the number of Saturdays and Sundays in the current year.

Weekends = CALCULATE(COUNT('Calendar'[Dayname]),FILTER('Calendar','Calendar'[Dayname]="Sunday"))
CALCULATE(COUNT('Calendar'[Dayname]),FILTER('Calendar','Calendar'[Dayname]="Saturday"))

The above measure gives a result of 104 days.

Now using variables below, we will get the same result.

Weekends =
var Sundays = CALCULATE(COUNT('Calendar'[Dayname]),FILTER('Calendar','Calendar'[Dayname]="Sunday"))
var Saturday = CALCULATE(COUNT('Calendar'[Dayname]),FILTER('Calendar','Calendar'[Dayname]="Saturday"))
return Sundays+Saturday

The variable Sunday holds the number of Sundays, and Saturdays contain the number of Saturdays in the year, and finally, we are calculating the sum. 

Improved Efficiency

In the below example, we will be calculating the Success Percentage of each student in a class. The student Names are stored in a Master table and the results in a child table, which will have the names of Passed students.

Actual Success Rate =
var x=values('Student_Master'[Name])
var a= CALCULATE(sum('Child_Table'[Maths]),'Child_Table'[Name] in x)
var b= CALCULATE(sum(''Child_Table' [Science]), 'Child_Table' [Name] in x)
var c= CALCULATE(sum(''Child_Table' [Computer]), 'Child_Table' [Name] in x)
var e =a+b+c
return if(ISBLANK(e),0,e)

Instead of reading the Master table each time for student name, it can be stored in a variable x, which can be used throughout the current scope. This reduces the number of hits to the Master dataset, which reduces the runtime of the expression thereby improving the efficiency.

Improved Readability

On Comparing the below and the above expression, the readability is more convenient while using variables within the expression. The below query makes it more cumbersome to read and understand. The variables make the expression easier to read and understand. They are self-describing.

Actual Success Rate = if(isblank(CALCULATE(sum('Table'[Maths]),'Child_Table'[Name] in values('Student_Master'[Name]))+ CALCULATE(sum('Table'[Science]), 'Child_Table' [Name] in values('Student_Master'[Name]))+ CALCULATE(sum('Table'[Computer]), 'Child_Table' [Name] in values('Student_Master'[Name])),0,CALCULATE(sum('Table'[Maths]),'Child_Table'[Name] in values('Student_Master'[Name]))+ CALCULATE(sum('Table'[Science]), 'Child_Table' [Name] in values('Student_Master'[Name]))+ CALCULATE(sum('Table'[Computer]), 'Child_Table' [Name] in values('Student_Master'[Name])))

Helps is step-by-step debugging.

While using variables a complex expression can be splitted into small chunks. Each expression can be evaluated once it’s created and can be checked if the result is as expected, and the same variable can be used in subsequent steps within the current scope. 

In the below example, say we are calculating the total number of hours in a year excluding Saturdays and Sundays. 

In the first step we are calculating number of Saturday and Sundays in a year

In the second step we are calculating the total hours in weekends

Similarly in the consecutive steps we’ll calculate the total number of hours in weekdays and finally, we arrive at the total number of hours in weekdays. In this way we can debug the variables and check the result. 

Reduces the complexity of the DAX 

Variables weren’t supported in earlier versions of DAX. Usage of functions like Earlier and Earliest to reference outer filter contexts were becoming complex. Generally, DAX variables are always evaluated outside the filters and are applied while returning the result, which provides the same result as that of the earliest function. This in turn reduces the complexity of writing a DAX.

Let’s see an example.

Here the products will be ranked based on their total prices within a range. Visually both the DAX provides same results. But the calculated column definition can be improved by using a variable instead of the EARLIER function. The CurrentSubcategorySales variable stores the Total Subcategory Sales column value in the current row context, and the RETURN expression uses it within a modified filter context.

Summary

Using variable in a DAX provides a same result,but in a more readable and understandable way. The result is stored within them upon their declaration and need not be recalculated everytime while using them. This improves the performance of a Measure or Calculated Column.

For more useful blogs, please visit Learn DAX