Mapping a column between two tables

[Anonymous]

I have this calculated table in power bi, that give me the unique department with its division and city and country, and don't forget it's calculated table with the following measure
UniqueCombinationSummary = SUMMARIZE( 'SQL_Database_Website',
'SQL_Database_Website'[Department],
'SQL_Database_Website'[City],
'SQL_Database_Website'[Country],
'SQL_Database_Website'[Division],
"UniqueDivisionCount", DISTINCTCOUNT('SQL_Database_Website'[Division]) )

and I also have a table called Viva Members Demographics and in the table, there is column (Department, City, country) the department and city and country column's values are almost matching the same columns in UniqueCombinationSummary and same wording, but there is no value for division, what i want is to be mapping the two table so i can know what is the division for the each existing department and city and country from Viva Members Demographics and exist in UniqueCombinationSummary for example if i have in UniqueCombinationSummary table a department called DAta analytics, and the city of it is New York, and the counrty is US and the division is Finance, while in the Viva Members Demographics table i have only department called DAta analytics, and the city of it is New York, and the counrty is US but i don't know what is the division, i don't have the data, we i need to check what is the division in UniqueCombinationSummary and write it in the new calculated column in Viva Members Demographics table and in this example it's Finance, but with very impotant considion, that only when the value in UniqueDivisionCount column is 1 so we can get only the unique matching division with its department, city and country

Also the relationship between the two tables is not working, what is the best way to do the mapping and get division data. 

Thanks a lot in advanced.

[Greg_Deckler · Accepted solution]

@Anonymous OK, so I would think you would first need to modify your calculated table to this:

UniqueCombinationSummary = 
ADDCOLUMNS(
  SUMMARIZE( 'SQL_Database_Website',
    'SQL_Database_Website'[Department],
    'SQL_Database_Website'[City],
    'SQL_Database_Website'[Country],
    'SQL_Database_Website'[Division],
    "UniqueDivisionCount", DISTINCTCOUNT('SQL_Database_Website'[Division]) ),
  "Key", [Department] & "|" & [City] & "|" & [Country] & "|" & [Division]
)

Then in your other table, a calculated column like this:

Division = 
  VAR __Department = [Department]
  VAR __City = [City]
  VAR __Country = [Country]
  VAr __Result = 
    MAXX(
      FILTER(
        'UniqueCombinationSummary',
        [UniqueDivisionCount] = 1,
        [Department] = __Department,
        [City] = [City],
        [Country] = __Country
      ),
      [Division]
    )
RETURN
  __Result

And then another calculated column in that table:

Key = [Department] & "|" & [City] & "|" & [Country] & "|" [Division]

Then relate your tables on the Key columns.