Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi All
I have two tables as shown below. I would like to lookup the latest input from Table Two and add the latest input into Table One as shown below. Please can anyone help to show me how to do it. Any help is appreciated. Many thanks.
Table One
Name | Latest Input |
A | testing 1 |
B | testing 5 |
C | testing 9 |
Table Two
Name | Input | Date and time |
A | testing 1 | 12/09/2023 15:23 |
A | testing 2 | 23/08/2023 09:30 |
A | testing 3 | 21/07/2023 12:25 |
B | testing 4 | 12/08/2023 15:23 |
B | testing 5 | 12/08/2023 16:30 |
B | testing 6 | 11/08/2023 15:00 |
C | testing 7 | 12/09/2023 09:00 |
C | testing 8 | 23/08/2023 15:00 |
C | testing 9 | 12/09/2023 16:00 |
C | testing 10 | 21/07/2023 12:00 |
Solved! Go to Solution.
@Apple08 Create a calculated column and use the below DAX
Latest Input Value =
VAR _max_date_time =
MAXX (
FILTER (
'Table 2',
[Name] = EARLIER ( 'Table 1'[Name] )
),
[Date and Time]
)
VAR _latest_input =
LOOKUPVALUE (
'Table 2'[Input],
'Table 2'[Name], [Name],
'Table 2'[Date and Time], _max_date_time
)
RETURN
_latest_input
Sorry, I forgot I also have a column 'Role' in table two and have to filter the role of user in table 2. I have tried to update the code to add in the lookup of 'Assistant' and 'Admin' as below, however it doesn't work. Please are you able to help.
Latest Input Value =
VAR _max_date_time =
MAXX (
FILTER (
'Table 2',
[Name] = EARLIER ( 'Table 1'[Name] )
),
[Date and Time]
)
VAR _latest_input =
LOOKUPVALUE (
'Table 2'[Input],
'Table 2'[Name], [Name] &&
'Table 2'[Role] in {"Assistant","Admin")
'Table 2'[Date and Time], _max_date_time
)
RETURN
_latest_input
@Apple08 Create a calculated column and use the below DAX
Latest Input Value =
VAR _max_date_time =
MAXX (
FILTER (
'Table 2',
[Name] = EARLIER ( 'Table 1'[Name] )
),
[Date and Time]
)
VAR _latest_input =
LOOKUPVALUE (
'Table 2'[Input],
'Table 2'[Name], [Name],
'Table 2'[Date and Time], _max_date_time
)
RETURN
_latest_input
Thanks very much for your prompt response Nandu! It works perfectly, I really appreciate your help! 🙂
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
57 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |