Cannot create relationship between two columns

[Anonymous]

I have SalesFact and DimCustomer table.

SalesFact have CustomerId, TransactionId, SalesAmt. There are thousands of rows for same CustomerID. (currently for testing I'm using only 1 CustomerId)

DimCustomer have CustomerId, CustomerName.

Power BI cannot make relationship between because is says that one of one of the tables must ba unique value. Should I create new calculated table or what should be done?

[MattAllington]

CustomerID must be unique in the customer table and also no blanks. 

[v-yuezhe-msft · Accepted solution]

Hi @Anonymous,

Create a new table using the following DAX expression.

CustomerId = 
FILTER(
DISTINCT(
UNION(
DISTINCT(SalesFact[CustomerId]),
DISTINCT(DimCustomer[CustomerId])
)
),
NOT(ISBLANK([CustomerId]))
)

Then create relationship among CustomerId , SalesFact and DimCustomer tables as follows.

Thanks,
Lydia Zhang