Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Aidan
New Member

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:

powerbi prob.JPG

 

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.

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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

 

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

3 REPLIES 3
v-piga-msft
Resident Rockstar
Resident Rockstar

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
TomMartens
Super User
Super User

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

 

 

 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

That's brilliant. Thanks very much!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.