The function takes a list of expressions as its parameters. It returns the first non-blank value in the list of expressions. If all the expressions are blank, the function returns a blank value.

Parameters of the COALESCE Function

The parameters of the COALESCE function are expressions that can be a column reference, a function that returns a scalar value, or a literal value. These expressions are evaluated in the order in which they are listed in the function. The function returns the first non-blank value that it encounters in the list of expressions.

Examples of Using the COALESCE Function

Let's take a look at some examples of using the COALESCE function in Power BI.

Example 1: Using COALESCE to Return the First Non-Blank Value

Suppose you have a table called "Sales" with the following columns: "Product Name", "Price USD", and "Price EUR". You want to create a DAX formula that returns the first non-blank price for each product. You can use the COALESCE function as follows:

This formula returns the first non-blank value from the "Price USD" and "Price EUR" columns for each product.

Example 2: Using COALESCE with Nested IF Statements

Suppose you have a table called "Orders" with the following columns: "Order ID", "Product Name", "Quantity", and "Price". You want to create a DAX formula that calculates the total revenue for each order, taking discounts into account. If the order quantity is greater than 10, the discount is 10%. If the order quantity is greater than 20, the discount is 20%. Otherwise, there is no discount. You can use the COALESCE function with nested IF statements as follows:

This formula calculates the total revenue for each order, taking into account the discounts based on the order quantity. The COALESCE function is used to return a value of 0 if the final discount calculation results in a blank value.

The Power BI DAX function COALESCE is a very useful function that can help you simplify your DAX formulas and make them more readable. It returns the first non-blank value in a list of expressions. The function takes a list of expressions as its parameters and returns the first non-blank value that it encounters in the list of expressions. You can use the COALESCE function to return the first non-blank value, even if the list of expressions contains nested IF statements or other complex calculations.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us