Lookup a third table to return a value

[Anonymous]

I have three tables as shown below. All three are unrelated and i dont want to move region name to country table.

I am looking for a calculated table as shown in output. 

Is this possible? Any help on this is much appreciated. thanks in advance.

[Anonymous]

@lbendlin , @v-rzhou-msft Could you please help on this

[v-rzhou-msft · Accepted solution]

Hi @Anonymous ,

Please try code as below to create a measure.

SUMX = 
VAR _Summarize =
    SUMMARIZE (
        DimRegion,
        DimRegion[Region Name],
        "Amount",
            VAR _Country =
                CALCULATETABLE (
                    VALUES ( DimCountry[Country] ),
                    FILTER ( DimCountry, DimCountry[Region] = MAX ( DimRegion[Region] ) )
                )
            RETURN
                CALCULATE (
                    SUM ( Country[Amount] ),
                    FILTER ( Country, Country[Country] IN _Country )
                )
    )
RETURN
    SUMX ( _Summarize, [Amount] )

Data model:

Result is as below.

Best Regards,
Rico Zhou

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

[Anonymous]

@v-rzhou-msft  Thank you very much for your solution. it works without any error.

[V_20]

@amitchandak could you please help on this