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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

DAX to get latest data from another table

Hello Everyone,

 

I hope someone can help me with this.  I have 2 tables, I need to get the latest value from the second table based on datetime

 

Table 1: 

 

IDRegion
1AMS
2APJ
3EMEA
4AMS
5APJ

 

Table 2 : 

 

IDMaturityLatest Update
1sample1.1  2/1/2021 15:45
2sample2.1  2/1/2021 4:00
3sample3.1  2/1/2021 0:00
4sample4.1  2/1/2021 0:00
5sample5.1  2/1/2021 0:00
1sample1.2  2/3/2021 0:00
1sample1.3  2/10/2021 4:00
2sample2.2  2/10/2021 5:00
3sample3.2  2/5/2021 0:00
3sample3.3  2/11/2021 18:00

 

 

result : 

 

IDRegionMaturityLatest Update
1AMSsample1.3  2/10/2021 4:00
2APJsample2.2  2/10/2021 5:00
3EMEAsample3.3  2/11/2021 18:00
4AMSsample4.1  2/1/2021 0:00
5APJsample5.1  2/1/2021 0:00

 

 

Thank you!

3 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Anonymous , New column in table 1

 

date = maxx(filter(Table2, Table1[ID] = Table2[ID] ),Table2[Latest Update])

 

new column =
var _max = maxx(filter(Table2, Table1[ID] = Table2[ID] ),Table2[Latest Update])
return
maxx(filter(Table2, Table1[ID] = Table2[ID] && Table2[Latest Update] = _max ),Table[Maturity] )

View solution in original post

PaulDBrown
Community Champion
Community Champion

@Anonymous 

 

Here is one way. First the model:

model.JPG

 

Now create the measure:

 

Latest Update (measure) =
VAR MXLU =
    MAXX (
        FILTER ( ALL ( Table2 ), Table2[ID] = SELECTEDVALUE ( 'dim Region'[ID] ) ),
        Table2[Latest Update]
    )
RETURN
    IF ( MAX ( Table2[Latest Update] ) = MXLU, MAX ( Table2[Latest Update] ) )

 

Set up a table visual (using the region fields from the dim table):

result.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Hi @Anonymous ,

 

Create a measure as below:

_Maturity = 
CALCULATE(MAX('Table 2'[Maturity]),FILTER('Table 2','Table 2'[Latest Update]=[_last update]&&'Table 2'[ID]=MAX('Table 1'[ID])))

And you will see:

v-kelly-msft_0-1613121140537.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

View solution in original post

7 REPLIES 7
PaulDBrown
Community Champion
Community Champion

@Anonymous 

 

Here is one way. First the model:

model.JPG

 

Now create the measure:

 

Latest Update (measure) =
VAR MXLU =
    MAXX (
        FILTER ( ALL ( Table2 ), Table2[ID] = SELECTEDVALUE ( 'dim Region'[ID] ) ),
        Table2[Latest Update]
    )
RETURN
    IF ( MAX ( Table2[Latest Update] ) = MXLU, MAX ( Table2[Latest Update] ) )

 

Set up a table visual (using the region fields from the dim table):

result.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

Hi Paul,

 

Thanks for this. The column for Maturity is sorted as Last Value. Can the measure that you provided be used in the Maturity as well if I have random values (cannot be alpha-sorted)?

@Anonymous 

 

Sorry, I don't understand what you mean. Care to provide an example/depiction?





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

sampleBI.PNG

 

For the column of Maturity, I was able to get the last value since it was sorted as "Last". I was thinking if there is a measure to get the latest value of Maturity based on date as some of the values will not be aphabetical.


really appreciate your wor here!

Hi @Anonymous ,

 

Create a measure as below:

_Maturity = 
CALCULATE(MAX('Table 2'[Maturity]),FILTER('Table 2','Table 2'[Latest Update]=[_last update]&&'Table 2'[ID]=MAX('Table 1'[ID])))

And you will see:

v-kelly-msft_0-1613121140537.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

 

I posted a measure which will return the rows with the latest date by region. So yes, it will return the latest "Maturity". Set up a table visual as I explained above





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






amitchandak
Super User
Super User

@Anonymous , New column in table 1

 

date = maxx(filter(Table2, Table1[ID] = Table2[ID] ),Table2[Latest Update])

 

new column =
var _max = maxx(filter(Table2, Table1[ID] = Table2[ID] ),Table2[Latest Update])
return
maxx(filter(Table2, Table1[ID] = Table2[ID] && Table2[Latest Update] = _max ),Table[Maturity] )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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