Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
dk1
Regular 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.

2 REPLIES 2
Anonymous
Not applicable

Not sure if you have seen, but the coallesce functions is now available in DAX

https://dax.guide/coalesce/

v-danhe-msft
Microsoft Employee
Microsoft Employee

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors