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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
pcav
Helper I
Helper I

Create column based on MAX date in associated table

I have a  data structure where a row in the Table1 has multiple associated rows in Table2. 


Each row in Table2 has a date and time column.

 

What I want to do is create a date and time column in Table1 with the MAX date and time value for the asssociated rows from Table2

 

 CaptureBI.PNG

 

 I have tried LASTDATE but it objects to the dates potentially being the same.

 

Can anybody help as I am struggling with this and have been for ages!!!!

1 ACCEPTED SOLUTION

@pcav

It will work anyway. Although in that case the FILTER( ) is redundant.  

View solution in original post

4 REPLIES 4
AlB
Super User
Super User

Hi @pcav

 

I understand you want to show in Table1 the latest date that appears in Table2 for that Id? If so you can create your new calculated column in Table1 as follows:

 

CalculatedDate =
CALCULATE ( MAX ( Table2[Date] ), FILTER ( Table2, Table2[Id] = Table1[Id] ) )

 

This assumes no relationships between Table1 and Table2

 

Thanks but that will not work as there is a relationship between table1 and table2 which is "Id"

@pcav

It will work anyway. Although in that case the FILTER( ) is redundant.  

Sorry I am being an idiot .... it will works perfectly if I type it in correctly!!!!

 

I haven't done that much Dax and sometimes stuggle by making things a lot more complicated than they need be.


Thanks very much for taking you time to help me  I very much appreciate it.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors