Max Date Filter per ID from another Table, using EARLIER (pbix included)

[Titatovenaar2]

Hi guys,

I'd like to view per a certain ID only the Max Date and values that correspond with that Max Date.

I got this working for ID's within the same table, but the solution doesn't work for a different Table so far.

Currently I use the following DAX in a calculated column, to apply as a filter on a Table:

SnapshotDatumMax_PerKlantID = 
IF(
    RaiAssessmentsV2[SnapshotDatum] = 
        CALCULATE( 
            MAX(RaiAssessmentsV2[SnapshotDatum]), 
            FILTER(RaiAssessmentsV2, RaiAssessmentsV2[KlantID] = EARLIER(RaiAssessmentsV2[KlantID])))
    ,TRUE()
    ,FALSE()
)

But I cannot simply change the Filter to the different table 'DIM Klanten':

I have a very simply dataset with 10 ID's only. Below you see the upper table where No filter is applied.

The filter is applied on the lower table, and as you can see this works fine for column 'KlantID', since it only shows the MAX information per KlantID. However, I want to be able to show the MAX information per 'Clientnummer' (second column). As you can see I still have 2 records in the lower table (pink rectangle). I only want the second of those 2 records.

Model:

The .pbix file:

View Max Date Only 

Any suggestions how to solve this?

Kind regards,

Igor

[Titatovenaar2]

I currently try via a calculated table instead of a column. This again works within the same table, but as soon as I try from a different table, it doesn't work. Related here doesn't work somehow.

TEST = 
SUMMARIZE(
    'DIM Klanten',
    'DIM Klanten'[Clientnummer],
    "Max Date",CALCULATE(Max(RELATED('RaiAssessmentsV2'[SnapshotDatum])))
)

It gives an error "the MAX function only accepts a column reference as an argument." However, RELATED passes a column, so I don't know how to solve this either.

Someone suggestions?

[amitchandak]

@Titatovenaar2 , earlier is for current table operations. Also, the max from the other tables will not work like this.

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[Titatovenaar2 · Accepted solution]

Using the calculated table with the SUMMARIZE() function solved my problem.

Make sure you summarize by whatever column you want, in my case the Clientnummer.

Followed up by the MAXX statement, that will return per record in the table (in this case, per Clientnummer) the maximum value of any column of another table (in this case, 'SnapshotDatum').

TEST2 = 
SUMMARIZE(  
    'DIM Klanten',
    'DIM Klanten'[Clientnummer],
    "Max SnapshotDate", MAXX(RaiAssessmentsV2, MAX(RaiAssessmentsV2[SnapshotDatum]))  
)

Cheers!

[Titatovenaar2]

Ok, I've seen those videos, and it was actually quite handy to look at those functions more closely.

However, in my situation I still don't know how to be able to filter a table using a MAX(date) based on an ID of a different table.

Any information on that?