Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

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? Any help on this is much appreciated. thanks in advance.

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    lbendlin , Anonymous Could you please help on this

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        Anonymous  Thank you very much for your solution. it works without any error.