Forum Discussion

anthony_dobson's avatar
anthony_dobson
Frequent Visitor
3 years ago
Solved

Match 2 data sources by latest keyid

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?

 

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    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.

1 Reply

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    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.