Relationships with edited data

[SharonHMA]

I am trying to create a relationship along 'Service Location' between two data sets (SLA Table and FSA Table) however I'm having trouble because the names are not unique in either table.

SLA Table contains Service Location Ascend (unique) which translates into Service Location (not unique - eg two Service Location Ascend can roll up to one Service Location for reporting purposes).

FSA Table is my market share geographic info.  Each FSA (unique) is rolled up to a Service Location (not unique).

I'm very new to Power BI. Would anyone have any suggestion on how I can create a unique field or find a work around that would allow me to link these data sets along Service Location.  

[Anonymous]

Hi @SharonHMA,

You can use dax formula to create a new table(distinct) based on “Service Location” and “Service Location Ascend”, then use the new table to link above tables’ columns.

For example:

Table = DISTINCT(UNION(VALUES(Sheet1[Code]),VALUES(Sheet2[Code])))

Regards,

Xiaoxin Sheng

[ankitpatira · Accepted solution]

@SharonHMA You can go to power bi desktop query editor -> create custom columns that can be join of two columns to make it unique or you can create calculated columns under Modelling tab that can be join of two other columns to make it unique.