Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Cannot create relationship between two columns

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?

1 ACCEPTED SOLUTION
v-yuezhe-msft
Employee
Employee

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.
1.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2

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



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
v-yuezhe-msft
Employee
Employee

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.
1.PNG


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors