Relationship with First Matching Record

[JFox]

Good morning!

I'm trying to create a relationship between two objects based on some criteria, and I'm having some difficulty coming up with the right definition for their relationship. In my data set I have Cases and Work Orders, where I might have many work orders to any one case. The important part of the data looks like this:

Case:

• ID

Work Order:

• Case ID
• Engineer Assigned
• Created Date

I need to create a one-to-one relationship between the two using only the first work order chronologically (by created date) where engineer assigned isn't blank. I've attempted to solve this myself by creating a custom table using the following:

FirstSiteVisitWO = SUMMARIZE(FILTER('Work Order', 'Work Order'[Engineer_Assigned] <> BLANK()), 'Work Order'[Case_ID],"FirstWO",MIN('Work Order'[Created_Date]))

The problem I've got now is that a good number of cases I'm expecting to see have been filtered out of my data set by this, and the ones I am seeing don't seem to consistently be the right work order associated. Can anyone suggest the correct way to make this related table?

Thanks!

[Icey]

Hi @JFox ,

Is there any problem? If none, please accept your reply above as a solution to close this post.

Best Regards,

Icey

[parry2k]

@JFox can you share pbix file with sample data, Remove any sensitive information before sharing.

[JFox]

@parry2kOkay, I've created a mockup without the reams of extra fields and gumph we're reporting on.  How do I attach a .pbix to the conversation here?  I only see attach buttons for photos and videos.

[parry2k]

@JFox share it thru onedrive/google drive, there is no method to directly attach the file here.

[JFox]

@parry2kI've dropped it up on dropbox, at:

https://www.dropbox.com/s/nggfj9744vwou7f/Case%20-%20Work%20Order%20Mockup.pbix?dl=0

Let me know if you can access that?  Our org has some pretty strict rules on file sharing via dropbox/onedrive etc...

[parry2k]

@JFox looking at file, what you are expecting the output to be. Description in the post doesn't match with the sample data, for example there is not create date in your model.

[JFox · Accepted solution]

@parry2kAfternoon again.  I may have rubber duck'd this with the example set.  Since that worked, I've rebuilt my larger data set and cut out a lot of chaff.  The numbers appear to be working, leading me to think it was a larger problem with the data set as a whole, rather than specific to this relationship.

[parry2k]

@JFox so you are good or need help, sorry not sure.

[JFox]

It'll be easier for me to make a mock-up rather than prune sensitive data out of the data set.  Give me a moment.