So this seems like it should be simple... but I've scrambled my brain so here goes for a first post.I have 2 Server DB tables (call them ref 2 like the below and ref 1 just containing account IDs).For reference purposes I want to add a column to ref 1 showing'Ref 2'[Op Date]for the MAX 'Ref 2'[EntryID]where 'Ref 2'[AccountID] matches the SelectedValue 'Ref 1'[AccountID].The 2 tables are linked by Account ID, Many to Many as there are unfortunately Duplicate entries in Ref 1 and filter both ways.I've tried a number of variations of the below but I can't unscramble my head and it has all returned #error to now.Last Entry Op Date = calculate('Ref 2'[Op Date],MAX('Ref 2'[Entry ID],'Ref 2'[AccountID] = Selectedvalue('Ref 1'[AccountID])Op DateEntry IDAccountID16/07/2023 14:47:35977476200737927470318/07/2023 00:34:21928914190475675277227/07/2023 06:15:10917214111935862204613/07/2023 12:00:13907003229085526734321/07/2023 09:55:44887242142468643948915/07/2023 17:57:47822298200737927470321/07/2023 08:07:27820539143081683592617/07/2023 06:30:52803237195267903530718/07/2023 23:59:41788496190475675277228/07/2023 05:17:58747561111935862204618/07/2023 22:56:34676736190475675277215/07/2023 12:41:15634347200737927470318/07/2023 01:54:30525484190475675277228/07/2023 14:23:35510121106057881272122/07/2023 15:52:06501205142468643948924/07/2023 02:31:54494670130726418004328/07/2023 12:39:25494629106057881272114/07/2023 22:17:13485105229085526734317/07/2023 19:57:22454365195267903530720/07/2023 21:48:10433631154279584404826/07/2023 12:52:34402046114052408863021/07/2023 22:57:08391310142468643948919/07/2023 23:19:38342824154279584404824/07/2023 22:57:24256698114236567270517/07/2023 12:52:57146705195267903530725/07/2023 11:06:57140118114236567270526/07/2023 00:50:12129247114052408863023/07/2023 14:07:20114029136874758746719/07/2023 03:46:591078281631713953852For Clarification for the shown examples I'd like to see...AccountIDLast Entry Created Date229085526734313/07/2023 12:00:13229085526734313/07/2023 12:00:13200737927470316/07/2023 14:47:35200737927470316/07/2023 14:47:35200737927470316/07/2023 14:47:35195267903530717/07/2023 06:30:52195267903530717/07/2023 06:30:52195267903530717/07/2023 06:30:52190475675277218/07/2023 00:34:21190475675277218/07/2023 00:34:21190475675277218/07/2023 00:34:21190475675277218/07/2023 00:34:21163171395385219/07/2023 03:46:59154279584404820/07/2023 21:48:10154279584404820/07/2023 21:48:10143081683592621/07/2023 08:07:27142468643948921/07/2023 09:55:44142468643948921/07/2023 09:55:44142468643948921/07/2023 09:55:44136874758746723/07/2023 14:07:20130726418004324/07/2023 02:31:54114236567270524/07/2023 22:57:24114236567270524/07/2023 22:57:24114052408863026/07/2023 12:52:34114052408863026/07/2023 12:52:34111935862204627/07/2023 06:15:10111935862204627/07/2023 06:15:10106057881272128/07/2023 14:23:35106057881272128/07/2023 14:23:35

Hi ALDOBES You can create a calculated column with below DAX in "Ref 1" table. Last Entry Created Date = var _maxEntry = CALCULATE(MAX('Ref 2'[Entry ID]),'Ref 2'[AccountID]=EARLIER('Ref 1'[AccountID])) return CALCULATE(MAX('Ref 2'[Op Date]),'Ref 2'[Entry ID]=_maxEntry) Best Regards,Community Support Team _ JingIf this post helps, please Accept it as Solution to help other members find it.

ALDOBES

Frequent Visitor

3 years ago

Solved

DAX Calc Column - Pulling a date from a different table with different last value and match criteria

So this seems like it should be simple... but I've scrambled my brain so here goes for a first post.

I have 2 Server DB tables (call them ref 2 like the below and ref 1 just containing account IDs).

For reference purposes I want to add a column to ref 1 showing

'Ref 2'[Op Date]
for the MAX 'Ref 2'[EntryID]
where 'Ref 2'[AccountID] matches the SelectedValue 'Ref 1'[AccountID].

The 2 tables are linked by Account ID, Many to Many as there are unfortunately Duplicate entries in Ref 1 and filter both ways.

I've tried a number of variations of the below but I can't unscramble my head and it has all returned #error to now.
Last Entry Op Date = calculate(
'Ref 2'[Op Date],
MAX('Ref 2'[Entry ID],
'Ref 2'[AccountID] = Selectedvalue('Ref 1'[AccountID])

Op Date	Entry ID	AccountID
16/07/2023 14:47:35	977476	2007379274703
18/07/2023 00:34:21	928914	1904756752772
27/07/2023 06:15:10	917214	1119358622046
13/07/2023 12:00:13	907003	2290855267343
21/07/2023 09:55:44	887242	1424686439489
15/07/2023 17:57:47	822298	2007379274703
21/07/2023 08:07:27	820539	1430816835926
17/07/2023 06:30:52	803237	1952679035307
18/07/2023 23:59:41	788496	1904756752772
28/07/2023 05:17:58	747561	1119358622046
18/07/2023 22:56:34	676736	1904756752772
15/07/2023 12:41:15	634347	2007379274703
18/07/2023 01:54:30	525484	1904756752772
28/07/2023 14:23:35	510121	1060578812721
22/07/2023 15:52:06	501205	1424686439489
24/07/2023 02:31:54	494670	1307264180043
28/07/2023 12:39:25	494629	1060578812721
14/07/2023 22:17:13	485105	2290855267343
17/07/2023 19:57:22	454365	1952679035307
20/07/2023 21:48:10	433631	1542795844048
26/07/2023 12:52:34	402046	1140524088630
21/07/2023 22:57:08	391310	1424686439489
19/07/2023 23:19:38	342824	1542795844048
24/07/2023 22:57:24	256698	1142365672705
17/07/2023 12:52:57	146705	1952679035307
25/07/2023 11:06:57	140118	1142365672705
26/07/2023 00:50:12	129247	1140524088630
23/07/2023 14:07:20	114029	1368747587467
19/07/2023 03:46:59	107828	1631713953852

For Clarification for the shown examples I'd like to see...

AccountID	Last Entry Created Date
2290855267343	13/07/2023 12:00:13
2290855267343	13/07/2023 12:00:13
2007379274703	16/07/2023 14:47:35
2007379274703	16/07/2023 14:47:35
2007379274703	16/07/2023 14:47:35
1952679035307	17/07/2023 06:30:52
1952679035307	17/07/2023 06:30:52
1952679035307	17/07/2023 06:30:52
1904756752772	18/07/2023 00:34:21
1904756752772	18/07/2023 00:34:21
1904756752772	18/07/2023 00:34:21
1904756752772	18/07/2023 00:34:21
1631713953852	19/07/2023 03:46:59
1542795844048	20/07/2023 21:48:10
1542795844048	20/07/2023 21:48:10
1430816835926	21/07/2023 08:07:27
1424686439489	21/07/2023 09:55:44
1424686439489	21/07/2023 09:55:44
1424686439489	21/07/2023 09:55:44
1368747587467	23/07/2023 14:07:20
1307264180043	24/07/2023 02:31:54
1142365672705	24/07/2023 22:57:24
1142365672705	24/07/2023 22:57:24
1140524088630	26/07/2023 12:52:34
1140524088630	26/07/2023 12:52:34
1119358622046	27/07/2023 06:15:10
1119358622046	27/07/2023 06:15:10
1060578812721	28/07/2023 14:23:35
1060578812721	28/07/2023 14:23:35

Need Help

v-jingzhang
3 years ago
Hi ALDOBES

You can create a calculated column with below DAX in "Ref 1" table.

Last Entry Created Date = var _maxEntry = CALCULATE(MAX('Ref 2'[Entry ID]),'Ref 2'[AccountID]=EARLIER('Ref 1'[AccountID])) return CALCULATE(MAX('Ref 2'[Op Date]),'Ref 2'[Entry ID]=_maxEntry)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

Forum Discussion

DAX Calc Column - Pulling a date from a different table with different last value and match criteria

3 Replies

Related content

Different schema into different destination

Date difference with multiple criteria

Different RLS criterias on different sheets

Page Level filter for different groups

Difference between different Staging lakehouses