COALESCE

How to Use the Power BI DAX function COALESCE

Syntax of the COALESCE Function

The syntax of the COALESCE function is as follows:


COALESCE(❰expression1❱, ❰expression2❱,…)


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:


First Non-Blank Price = COALESCE([Price USD], [Price EUR])


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:


Total Revenue = [Quantity] * COALESCE(IF([Quantity]❱20, 0.8, IF([Quantity]❱10, 0.9, 1)) * [Price], 0)


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

15-17 July 2024
16-18 September 2024
11-13 November 2024

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)