Reference Unrelated Field (many-to-many) on a Different Table w/the Earliest Instance

[stbrooks13]

I have two tables that I cannot relate because there are multiple instances on each.

One table (called ActivityTable) is a list candidates tied to jobs.

The other table (called OCITable) is the same list of candidates, but it also includes the "Interview Date" that the candidate interviewed with us, sometimes more than once.

The "Candidate ref num" exists on both tables and is the field that *should* tie the two together.

What DAX formula do I use on the ActivityTable to show the earliest interview date found on the OCITable?

Thank you in advance for your help!

 

[Ozeroth]

Hi stbrooks13,

To make your life easier, you should create a common lookup table for Candidate ref num and relate both ActivityTable and OCITable to that.

Once you've done that, within the row context of the ActivityTable, you can access the OCITable using CALCULATE. CALCULATE results in context transition which creates a filter context corresponding to the current row of the extended ActivityTable (which includes the Candidate lookup table), which in turn filters OCITable.

The calculated column would simply be

= CALCULATE ( MIN ( OCITable[Interview Date] ) )

Dummy example here:
https://www.dropbox.com/s/kj6fl4i0oa012hg/Earliest interview date.xlsx?dl=0

Regards,
Owen

 

[stbrooks13]

Thank you! I'm not entirely sure what's happening but it's only returning values from 2012 and 2016.

 

[Ozeroth]

No worries - so are the values returned correct or not?
If not, could you post a link to a sanitised model or an extract of the tables showing where results are not as expected?