Retrieving Value from other table based on a condition and the distinct row value

[Cleo5]

Hi there,
for most of my challenges I found solutions within this forum. However, not for this one. I'm stuck in the thinking process and need support.
I have one imported table ('MBRMIG_Status') and one calculated table ('LatestMigStatusForEachMBR) which is related to the imported table via the "Vorgangsschluessel" (Document name)

The imported table has besides the three imported columns

two other calculated columns:

MigStatusGroup = IF(SUMX(Migrationstatus, FIND(UPPER(Migrationstatus[Migrated]), UPPER(MBRMIG_Status[LatestStatus], ,0)) > 0, "Migrated", BLANK())

Instead of the Blank() there are 4 more If-conditions just with different status group wordings (besides "migrated", also "finished," "OtherStatus", ...).

LatestTransitionDate = CALCULATE(MAX(MBRMIG_Status[Transition to status last date]), ALLEXCEPT (MBRMIG_Status, MBRMIG_Status[Vorgangsschluessel]))

Telling me what was the last date something changed for the specific document ("Vorgangsschluessel).

My calculated column

was created based on having each "Vorgangsschluessel" just once:

LatestMigStatusForEachMBR = DISTINCT(SELECTCOLUMNS(MBRMIG_Status, MBRMIG_Status[Vorgangsschluessel]))

and adding again the latest date:

LatestTransDate = CALCULATE(MAX(MBRMIG_Status[Transition to status last date]), ALLEXCEPT (LatestMigStatusForEachMBR, LatestMigStatusForEachMBR[MBRMIG_Status_Vorgangsschluessel]))

Now I struggle to fetch the MigStatusGroup from my 'MBRMIG_Status'-Table specific for the document name ("Vorgangsschluessel") and where the "LatestTransDate" (calulcated table) is the same as the "Transition to status date" (imported table).

LatestStatusOnThisDate = CALCULATE(
    COUNTROWS(MBRMIG_Status),
        FILTER(MBRMIG_Status, MBRMIG_Status[LatestTransitionDate] = LatestMigStatusForEachMBR[LatestTransDate])
        ALLEXCEPT(LatestMigStatusForEachMBR, LatestMigStatusForEachMBR[MBRMIG_Status_Vorgangsschluessel])
    )

Solution should look like this:

Any suggestions?

[v-zhangti · Accepted solution]

Hi, @Cleo5 

You can try the following methods.

Sample data:

Result:

LatestTransDate = LOOKUPVALUE('Table'[LatestTransDate],'Table'[Vor],[Vor])

Column = CALCULATE(MAX('Table'[MigStatusGroup]),FILTER('Table',[Transition to status last date]=EARLIER('Table 2'[LatestTransDate])))

Refer to the attachment.

Best Regards,

Community Support Team _Charlotte

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

[Cleo5]

Solution was found by using more arguments in Lookupvalue

LatestStatusOnThisDate = LOOKUPVALUE(MBRMIG_Status[MigStatusGroup], MBRMIG_Status[Vorgangsschluessel], LatestMigStatusForEachMBR[MBRMIG_Status_Vorgangsschluessel], MBRMIG_Status[Transition to status last date], LatestMigStatusForEachMBR[LatestTransDate])