DAX Functions

DAX (Data Analysis Expressions) is a powerful feature of Power BI, by which complex measures, calculated columns, and tables can be created. DAX helps in creating new metrics and does provide information for data analysis problems that are hidden within the data set and helps in identifying KPIs for business needs.

DAX may seem to be similar to excel functions, but certain functions are unique to Power BI DAX and its uses for creating new information help the analyst to think from a different perspective, DAX measures are easy to build with its built-in functions like Table manipulation functions, Time intelligence functions, Relationship functions, etc.

In this Topic, I am going to cover the most commonly used DAX functions in Power BI and how often it helps in creating new measures and KPIs for analyzing the data.

One of the best and most commonly used DAX functions is CALCULATE ()

• CALCULATE ()

This function is a filter function that performs the expression based on the filter that will return a scalar value. Its application to create measures using the filter option helps the user to apply the calculate function more frequently. Calculate function is the most used filter function by Power BI users.

Syntax:

CALCULATE(<expresssion>[,<filter1> [, <filter2> [, ...]]])		

Term Definition

expression The table expression to be evaluated

filter1,filter2... (optional) Boolean expressions or table expressions that define filters

The expressions specify any aggregate function which is similar to the measure.

Filter in the function specifies

• Boolean filter expression

• Table filter expression

• Filter modification functions

The filter can use a logical operator yet all the filter's output must be TRUE or used by the OR operator.

*Note Boolean filter can refer to columns from a single table, can contain an aggregate function that returns a scalar value, cannot use functions that scan or return a table unless passed as arguments to aggregation, cannot refer to measures, and cannot use a nested CALCULATE function.

Writing a Measure can be done in two ways.

First Method:

The below method defines a structured way of depicting the formulas as we can see variables are defined first and use those variables in the Measure Function, in this way we can read the formula better when we are creating a complex formula.

Using VAR and Return
  Multiple Orders =
    VAR sale_quantity = Sales[orderquantity] > 1
    RETURN
      CALCULATE(SUM[Total_orders], sales_quantity)

Second Method:

This method is a commonly used formula writing method where all the filters or sub-function are written within the main function of the measure.

Multiple Orders = CALCULATE(SUM[Total_orders], Sales[Orderquantity]>1)

• CALCULATETABLE ()

This function is a filter function that performs the same functionality as calculate function, the result of this function will be a table object.

Syntax:

CALCULATETABLE(<expression>[, <filter1> [, <filter2> [, ...]]])

Term Definition

expression The table expression to be evaluated

filter1,filter2... (optional) Boolean expressions or table expressions that define filters

* We can also perform DAX query using DEFINE & EVALUATE Keywords

-- Returns all brands of Bicycle
Evaluate
CALCULATETABLE (
    VALUES ('Product'[Brands] ),
    'Product'[Category] = "Bicycle"
)

• SELECTEDVALUE ()

This function is a filter function that returns the column value when filtered from a column name to one distinct value only, if the results are not a single value the return will be an alternative result, we can also return the slicer-filtered value through this function.

Syntax:

SELECTEDVALUE(<columnName> [, <alternateResult>])

Term Definition

columnName Column name of an existing table, cannot be an expression

alternateResult by default blank, applicable only when the o/p value is zero or more

*Note: There is an alternative way for SELECTEDVALUE which is the HASONEVALUE function, we will see the function next section.

-- Returns the selected color in a filter 
Product_color = SELECTEDVALUE(Product[color], "More than one value")

• HASONEVALUE()

This is an information function that returns TRUE or FALSE only, The result is TRUE when the columnName is filtered down into a distinct single value, otherwise, it returns FALSE.

Syntax:

HASONEVALUE(<columnName>)

Term Definition

columnName Column name of an existing table

*Note HASONEVALUE functions similar to SELECTEDVALUE, Let's see an example.

-- Returns the selected color in a filter
IF(HASONEVALUE(Product[color],VALUES(Product[color]),"More than one value")

• VALUES()

This function is a table manipulation function that can be used as an intermediate or can be nested in a formula to provide distinct values that can be counted, filtered, or sum other values.

Syntax:

VALUES(<TableNameorColumnName>)

Term	Definition
TableName or ColumnName	Returns unique values from a column, Returns rows from a table

*Note This function cannot be used to return values in a cell or column in a sheet.

-- Returns the summation of order quantity
SUM(VALUES(Product[order_quantity]))

• SUMMARIZE()

This function is a table manipulation function that returns a summarized table based on the group's option from a table of data.

Syntax :

SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]..[, <name>,<expression>]...)	

*Note This function doesn't provide any scalar values or column values but summarizes them into a table as required.

SUMMARIZE(Sales,
        (Date[Year],Product[Category]),
        "Sale Amount", SUM(Sales[Amount]),
        "Sale_quantity", SUM(Sales[Quantity])
       )

*Stay tuned for more useful and advanced functions for Power BI Analysis.