Forum Discussion

Aidan's avatar
Aidan
New Member
7 years ago
Solved

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

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.

Need Help
  • TomMartens's avatar
    TomMartens
    7 years ago

    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

     

     

     

3 Replies

  • TomMartens's avatar
    TomMartens
    Super User
    7 years ago

    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's avatar
      Aidan
      New Member
      7 years ago

      That's brilliant. Thanks very much!

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    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