# 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) Power BI DAX Intensive Training Course £1,260.00 – £31,860.00 Select optionsContinue Loading Done Power BI DAX Introduction £474.00 – £11,700.00 Select optionsContinue Loading Done Power BI DAX Intermediate £474.00 – £11,700.00 Select optionsContinue Loading Done Power BI DAX Advanced £474.00 – £11,700.00 Select optionsContinue Loading Done Upcoming Courses 15-17 July 202416-18 September 202411-13 November 2024 Contact Us Subject Your Name (required) Company/Organisation Email (required) Telephone Training Course(s) Power BI Intensive TrainingPower BI introduction Power BI IntermediatePower BI AdvancedDAXPower Query MPower BI CertificationPower BI AdministrationPower PlatformPower AutomatePower AppsOTHER Your Message Upload Example Document(s) (Zip multiple files) ```
``` ```
``` ```
``` ```
``` ```
``` ```