Power bi equivaent of sql Coalesce across multiple datasets

[Arranafc19]

Hi 

I am wondering whether there is a Power Bi equivalent of Coalesce that I could use taking on fields from multiple datasets ?

I have a scenario where I have one main dataset which stores all my products , with a relationship created with other datasets for the orders.

I have a product table with looks like below where the product ID is a unique id :

Product ID | Name | Date Created 

I then have multiple datasets which store the order information for the products depending on the type like below :

Product ID | Date Sold | Current Status 

There are three different datasets for the above format , BikeOrders , CarOrders , VanOrders with the product ID of the main table existing in one of these tables , depending on the type of product and relationship has been created between these based on the product id.

What I need to do is create a field on the main dataset (Product) called Order status which will pull in the status from the dataset where the product ID exists .

Is there a dax function that will allow this ?

Effectively it needs to work like the following :

If (bike orders status is null , check car orders status , if that is null then vanorders etc)

Can anyone assist with this ?

[v-yiruan-msft]

Hi @Arranafc19 ,

Please review the following documentations and check if they can achieve your requirement:

DAX Coalesce Function in Power BI

From SQL to DAX: Implementing NULLIF and COALESCE in DAX

How can I perform COALESCE in power query?

Best Regards

Rena

[amitchandak]

we have coalesce and we take data from multiple data set , but can not take ungrouped. so we need to have some common level to the group .

See this example how diff has taken from two dates by having them from two different tables but choosing a common dimension/level

https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

[HotChilli]

COALESCE is relatively new to PowerBi but it is in DAX now.  Available in current version of powerbi, for sure.

You should be able to write a COALESCE statement to get the status from each of the xxxorders tables.  You might need RELATED depending on the nature and direction of the relationships.

Also, the data model looks a little unusual.  It looks like the Product table is the Fact table and each of the Orders tables are dimensions.  It is more common to put all the Orders as a fact table and use Product table as dimension.  Depends on what is being reported on, of course.