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 Tab...
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

     

     

     

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!