Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello all,
I'm new to Power BI, and I'm trying to manage relationships between 3 tables -- "Customers", "Sales Orders", and "Cash Sales".
The Customers table includes a list of all current customers with a 3 month time period (Q3 2015).
Ex:
Customers
Customer 123
Customer 456
Customer 789
The Sales Orders table includes the Sales Orders for each of those customers
SO# Customers
SO-400 Customer 123
SO-401 Customer 456
SO-402 Customer 789
To illustrate, I have a Sales Order with 3 distinct items, so in the Cash Sales table, it displays the same Sales Order number in three consecutive rows right next to 3 different item numbers.
Ex:
SO# Item# Customers Date of Sale
SO-401 9900 Customer 456 09/01/2015
SO-401 9921 Customer 456 09/01/2015
SO-401 9931 Customer 456 09/01/2015
I want to create a relationship for the "Customers" column in the Customers table to the same column in the Sales Orders table, which works perfectly. The problem I seem to have is when I try to create a relationship between the SO# column in the Sales Orders table and the Cash Sales table (due to the Cash Sales table having duplicate values for the SO#).
Is there any way to "roll up" the duplicate values in the SO# column so I can create the relationship with unique values?
Thank you in advance to anyone that can help! 🙂
Do you have duplicate SO# in the Sales Orders Table?
What error message do you get when you try to relate the 2 tables?
No, I only have unique SO#s in the Sales Orders table, but I have duplicate Customers (due to Customers that placed more than one order within the Q3 2015 time frame).
Ex:
SO# Customers
SO-101 Customer 123
SO-102 Customer 123
SO-103 Customer 123
SO-121 Customer 456
SO-122 Customer 456
SO-131 Customer 789
SO-132 Customer 789
The error message I get is:
"We cannot create a relationship between 'Sales Orders'[SO#] and 'Cash Sales'[SO#]. This could be because there is missing intermediate data to connect the two columns."
What error do you get when you try to relate? Can you post a screenshot of the error message?
Based on the sample you've provided everything should work!
@Sean Yes, I did have blanks, so I went to the Query Editor and created a Custom Column (that I named 'SO#2') and used a formula to fill the blank SO#s with the Cash Sale number:
= if [SO#] = null then [CS#] else [SO#]
I then tried creating a relationship between Sales Orders [SO#] and Cash Sales [SO#2] to see if the issue was resolved, but I'm still met with the same error message.
@TaylorVA wrote:
@Sean Yes, I did have blanks, so I went to the Query Editor and created a Custom Column (that I named 'SO#2') and used a formula to fill the blank SO#s with the Cash Sale number:
= if [SO#] = null then [CS#] else [SO#]
I then tried creating a relationship between Sales Orders [SO#] and Cash Sales [SO#2] to see if the issue was resolved, but I'm still met with the same error message.
blank is not quite equal to null. Try
= if [SO#] = null or [SO#] = "" then [CS#] else [SO#]
With the new formula... is [CS#] unique (no dupes)?
I'll just say that in 3 years of doing power pivot... this error has *never* been wrong. I have always had an unexpected duplicate (blanks being a great gotcha).
The one side has to have every value of the many side! (Many side does not need to have every value of 1 side)Basically every value in the many table should be able to be matched in the one table.Seems to me those new numbers you put in place of blanks in the new column would not be matched on the 1 side.
Actually, that is not true. Obviously it is better of the 1 side has all the values, but it will let you create the relationship as long as there are no dupes.
@Sean wrote:
The one side has to have every value in the many side! (Many side does not need to have every value in 1 side)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
106 | |
78 | |
72 | |
67 |
User | Count |
---|---|
141 | |
107 | |
100 | |
82 | |
74 |