Match 2 data sources by latest keyid

[anthony_dobson]

Hey I have 2 tables both containing PCName, KeyID, KeyDateTime for 2 different data backup sources A and B, where the PCNames are related many:many. Multiple KeyIDs can exist for a PCName and the KeyIDs may or may not exist in both sources.

I want to query the the latest KeyID by KeyDateTime for the PCName exists in both data sources that is Compliant, else report if table A or B has a newer KeyID that only exists in either table.

Any ideas on how to tackle this?

[v-yanjiang-msft · Accepted solution]

Hi @anthony_dobson ,

According to your description, I create a sample.

TableA:

TableB:

Here's my solution, create a measure:

Latest_keyID =
MAXX (
    FILTER (
        'TableA',
        'TableA'[KeyID]
            = MAXX (
                FILTER (
                    ALL ( 'TableB' ),
                    'TableB'[PCName] = MAX ( TableA[PCName] )
                        && 'TableB'[KeyDateTime]
                            = MAXX (
                                FILTER ( ALL ( 'TableB' ), 'TableB'[PCName] = MAX ( TableA[PCName] ) ),
                                'TableB'[KeyDateTime]
                            )
                ),
                'TableB'[KeyID]
            )
    ),
    'TableA'[KeyID]
)

Get the latest KeyID by KeyDateTime for the PCName exists in both data sources:

If you want to count the number of PCName meet the above condition, create a measure:

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'TableA'[PCName] ),
    FILTER (
        ALL ( 'TableA' ),
        'TableA'[KeyID]
            = MAXX (
                FILTER (
                    ALL ( 'TableB' ),
                    'TableB'[PCName] = EARLIER ( TableA[PCName] )
                        && 'TableB'[KeyDateTime]
                            = MAXX (
                                FILTER ( ALL ( 'TableB' ), 'TableB'[PCName] = EARLIER ( TableA[PCName] ) ),
                                'TableB'[KeyDateTime]
                            )
                ),
                'TableB'[KeyID]
            )
    )
)

Get the correct result:

I attach my sample below for your reference.

Best regards,

Community Support Team_yanjiang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.