DAX Calculate and Values Functions

CALCULATE Function

In Power BI, the CALCULATE is a fundamental and very useful function. This function is used to evaluate an expression by applying filters, conditions and any calculation based on the context.

Syntax:

CALCULATE(Expression, [Filter1], ...)

Example:

CalculateDEMO = CALCULATE(SUM('Global-Superstore'[Sales]),'Global-Superstore'[Country] = "United States")

Example:

CalculateFiltersDemo = CALCULATE(SUM('Global-Superstore'[Sales]),
'Global-Superstore'[Country] = "United States" ||  
'Global-Superstore'[Country] = "Germany")

VALUES Function:

In DAX for Power BI, the VALUES function is used to retrieve distinct values from a column.

Syntax:

VALUES(TableORColumnName)

Example:

Number of cities = COUNTROWS(VALUES(City[CityName]))

Using the FILTER Function in DAX

The Power BI DAX, the FILTER function is used to get data from a table or an expression based on conditions applied. It is generally used in combination with other functions.

Syntax:

FILTER(Table, Filter OR Condition) 

Example:

FilterDAX1 = Calculate(Sum(Forecast[Forecast]),FILTER(Forecast, Forecast[Country]="USA" || Forecast[Country]="Italy"))

Variable for Debugging

In DAX variables can be used to store and reuse value stored in the variable within a formula. The variables in a DAX formula make it more readable, easier to maintain and helps in reusing values store in variables.

Below is the Syntax of how a variable is used:

VAR variable_name = expression

RETURN result_expression

The keyword ‘VAR’ indicates the beginning of the variable declaration.

The keyword ‘RETURN’ indicates the beginning of the expression that will return the result.

Below is an example of using variables:

DiscountOffered = VAR SaleQuantity = SUM('Order Data'[Cookies Shipped]) 
VAR CostOfCookies=SUM('Order Data'[Cost]) 
Return IF(SaleQuantity > 800, CostOfCookies * 10/100, SaleQuantity * 5 / 100)

The ‘DiscountOffered’ is a measure created with 2 variables to calculate the discount based on no of cookies shipped using an if condition.

The below is the output

DAX (Data Analysis Expressions) Measures in Power BI are standard aggregation functions or dynamic custom expressions defined with the DAX formula language. Using this formula language, you can calculate, combine, and filter data that can return one or more values. DAX Measures are dynamic and user-defined formulas where results change depending on context. This article discusses the DAX Measures in Power BI, how to create and edit, and how to use them in Power BI Visualization reports.