How to make a new column from two tables with relationship many to many

[yem19397]

Hi All,

I want to make a new column "Handling Date" which is the date difference between OrderDate (from table A) and ShipDate (from table B). The relationship between table A and table B is many to many. How to do this?

I tried with:

Handling Date= DATEDIFF('B'[ShipDate],RELATED('A'[OrderDate]),DAY)

But it doesn't work.

Thanks,

Michael

[johncolley]

Hi @yem19397 , Can you provide more information on what doesn't work - there could be a number of issues causing this.

For example the order in which your formula is written is probably going to produce negative values as the shipping date is likely to be after the order date. 

Or the issues could be stemming from your many-to-many relationship (which should be avoided majority of the time). What fields is the relationship using? Are you able to create a 1 - Many relationship using something like an Order ID?

[yem19397]

Hi John,

Thanks for your reply. 

I used Handling Date= DATEDIFF('A'[OrderDate],RELATED('B'[ShipDate]),DAY)

It also doesn't work.

The error message is:

The column 'B'[ShipDate]' either doesn't exist or doesn't have a relationship to any table available in the current context.

The relationship between A and B is many to many and cannot be changed to 1-many  or 1-1. The relationship between the two tables is linked based on Order ID. I think other function, in addition to related(), should also be used and involved.

Thanks,

Michael

[johncolley]

Hi @yem19397 , Thanks for providing some more information. My first suggestion would be to try and remove the many-to-many relationship with a bridging table - see here: https://www.seerinteractive.com/blog/join-many-many-power-bi/ 

If you have to use many to many - related function won't work. You could try Lookupvalue as suggested here: https://community.powerbi.com/t5/Desktop/DATEDIFF-between-two-tables-many-to-many-relationship/td-p/...

Please provide some sample data if you require any further assistance.