Calculate Max Value Column from Many-to-Many Related Column

[Aidan]

I have two tables:

• Table_1 is a history log of changes to an entity (ID) with change date and revision number
• Table_2 is a simple list of ID and dates
• I want Table_3, which is an extension of Table_2, with an additional column that shows the maximum revision (from Table_1) for matching ID's where the change occured (Changed_Date in Table_1) before the Date in Table_2.

Simple visualization:

Table_1 and Table_2 are related, many-to-many via a bridging table (distinct on ID). I can't seem to find the right set of functions to get me the value I want. Any help greatly appreciated!

Thanks in advance.

[v-piga-msft]

Hi @Aidan,

By my tests, the solution of TomMartens should be helpful.

If you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

[TomMartens · Accepted solution]

Hey,

I'm using the following DAX statement to create a calculated column in Table_2, maybe this is already sufficient and it's not necessary to create a Table_3:

Last Revision Number = 
var thisID = 'Table_2'[ID]
var thisDate = 'Table_2'[Date]
return
MAXX(
    TOPN(
        1
        ,FILTER(
            ALL('Table_1')
            ,'Table_1'[ID] = thisID && 'Table_1'[Changed_Date] <= thisDate
        )
        ,'Table_1'[Changed_Date]
        ,DESC
    )
,[Revision Number]
)

Please be aware that I'm using <= instead of just < because there is no revision number before 2018-11-01.

Regards,

Tom

[Aidan]

That's brilliant. Thanks very much!