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

     

     

     

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