PowerBI DAX – VAR

By | June 16, 2020

A very useful way to work with DAX functions is to use VAR. VAR is a form of variable in which results can be stored temporarily without having to create measures and then using them in others. For example you use Var to store the result of a particular group and then use the var output to calculate something similar to Percentage total

The below DAX formula shows an example of the above calculation

PercentEarning = VAR YearlyRevenue = Sum('Fact Order'[Total Excluding Tax]) VAR TotalRevenue = CALCULATE(SUM('Fact Order'[Total Excluding Tax]),ALL('Dimension Date')) return if(TRUE(),YearlyRevenue/TotalRevenue)

The screenshot below shows what it would look like inside PowerBI

The SQL equivalent of the above DX function is similar to the query below

SELECT @var   DECLARE @var FLOAT = (SELECT sum([Total Including Tax]) FROM fact.[order] o WHERE [Order Date Key] IN (SELECT DATE FROM Dimension.DATE WHERE [Calendar Year] = '2013'     AND [Calendar Month Number] = 2 ) ) / (SELECT sum([Total Including Tax]) FROM fact.[order] o WHERE [Order Date Key] IN (SELECT DATE FROM Dimension.DATE WHERE [Calendar Year] = '2013' ) )

SELECT @var