Forum Discussion
Lookup a third table to return a value
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?...
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 ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
lbendlin , Anonymous Could you please help on this
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 Thank you very much for your solution. it works without any error.