Calling out all DAX experts!

[Anonymous]

Hi!

We are making a date conflict report, where the date conflict arises when there is a sales order with Sales line (s), where the item (on the sales line) is not in stock (or reserved for something else), and the date of receipt on the purchase order is before the agreed shipping date on the sale order. Each time the conflict scenario appears, it must be counted as a row with a conflict. The scenario counter is pinned to a Dashboard up on Powerbi.com, and based on that, an mail is generated via Power Automate to the person responsible for the sales order (that part is under control).

However, there is NO direct relationship from the purchase order line to the sales order line, only through the dimension table "Items". It looks like the following (see relation picture):

Much works in our current measure, however, we can not create the relationship from Factsalesline to FactPurchOrder, and since one of the built-in measures compares the receipt date of the purchase order with the dispatch date of the sales order, it returns empty values. The formula made sense as follows:

We have even tried to drag an inactive relationship between item number from FactpurchOrderline to items from FactSalesline item number (however this is a many to many relationship) and then insert USERELATIONSHIP in the above measure - this did not work .. Maybe we insert it incorrectly .. There must be a way to do it - we agree that there can easily be several purchase order lines with the same item number, which is why there are several different receipt dates. We are ONLY interested in the first receipt date per item.

Thanks!

BR

RUNE

[Greg_Deckler]

@Anonymous Check out the use of CROSSFILTER and/or TREATAS to build the relationship within the measure itself. Beyond that, would need sample data to test with.

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, 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

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Hi Greg,

I expects and want to get this out of the measure:

Then I want to count the no of scenarios where we have a sales order with items, that are set to be received after we agreed with the customer to ship it.

Now the measure returns empty recieve dates..

BR

Rune