cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dk1 Visitor
Visitor

Coalesce function in DAX (DirectQuery)

Hi all,

I am working in Power BI desktop to build a shipping report. There are three potential dates: Delivery Schedule date, Order Line Item Date, and Order Date. Each of these comes from a separate table, but they all have proper relationships established between them.

Here is what I want to write:

  1. Check for Delivery Schedule date. If there is one, use it. If there is not one, go to Step 2.
  2. Check for Order Line Item date. If there is one, use it. If there is not one, use Order Date.

I have an old SQL-based reporting program which used the Coalesce function to make this happen. DAX does not have a Coalesce function. I tried using the SWITCH and ISBLANK functions from an old thread that I found on Google. https://community.powerbi.com/t5/Desktop/DAX-equivalent-of-SQL-Coalesce/td-p/220365

I was not successful with this. Power BI refuses to show the visual.

 

The tables are linked together by a unique Customer Order ID, but not every order has a delivery schedule, so there are many orders for which a row does not even exist in the Delivery Schedule table. If I use the SWITCH function and ISBLANK to check that column for a date, it's not encountering a null/blank value - it's that there's not even a row there corresponding to that order ID. Maybe this is what's causing the problem?

 

I've got everything together just fine on this report except for the date.

 

Any help or hints would be greatly appreciated!

Thanks.

1 REPLY 1
Community Support Team
Community Support Team

Re: Coalesce function in DAX (DirectQuery)

Hi @dk1,

Could you please offer me some sample data to have a test and post your desire resullt if possible?

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.