Forum Discussion
Dataset Relationship Issues
Hello together,
I have a small problem and hope you can help me. Surely this is quite simple and I just do not get it.
So I have to two datasets. One has the information about ordered amounts for several customers and the other one about offered for some of these customers.
For Example:
Table A
| Customer | ordered amount |
| A | 10 |
| A | 5 |
| B | 20 |
| B | 10 |
| C | 5 |
Table B
| Customer | offered amount |
| A | 10 |
| B | 20 |
| B | 10 |
In the end I want a barchart of all customers to compare the ordered and offered amount.( also for example of "c" which is not yet included in table B.
Of course I created a table in powerbi with all customers of table a and b and created a relationship (many to many only possible).
The problem: it doensn't show "c" just the customers which are included in both tables.
Where is my mistake?
Thank you!
Hey Anonymous ,
based on the sample data (Table A and Table B) I created a little sample pbix file, see attachment.
I used this DAX statement to create a third table Customer:Customer = DISTINCT( UNION( ALLNOBLANKROW( 'Table A'[Customer] ) , ALLNOBLANKROW( 'Table B'[Customer] ) ) )Then I created relationships like below:
This allows to create a simple data visualization like the one below:If you are using Power Query to create the Customer table, you have to make sure that each Customer appears just once in the Customer table.
Hopefully, this helps to tackle your challenge.Regards,
Tom
3 Replies
Hey Anonymous ,
create a third table that contains all the customers from table A and table C using this pattern:
Customer = DISTINCT( UNION( ALLNOBLANKROW('table a'[customer]) , ALLNOBLANKROW('table b'[customer]) ) )Create relationships between the new table and the existing tables, the new table reflects the the one-side of the relationships.
There is no need for a many-to-many relationship.
Here you will find a great start to data modeling: https://docs.microsoft.com/en-us/learn/modules/model-data-power-bi/
Hopefully, this will help to tackle your challenge.
Regards,
Tom
Hello Tom,
first of all thank you for your answer. And for the tutorial of starting with data modelin 😛 (ich war leicht getriggert)
Like I already desriceb I have already created a third table. (quite the same formula like you and one time with power query editor)
The problem is:
After I create a releationship the only connection which is possible, is many to many. Another one is not possible. ("The cardinality you selcted isn't valid for this relationship")
So I still have the problem, that I can't display all customers.
thank you!
Hey Anonymous ,
based on the sample data (Table A and Table B) I created a little sample pbix file, see attachment.
I used this DAX statement to create a third table Customer:Customer = DISTINCT( UNION( ALLNOBLANKROW( 'Table A'[Customer] ) , ALLNOBLANKROW( 'Table B'[Customer] ) ) )Then I created relationships like below:
This allows to create a simple data visualization like the one below:If you are using Power Query to create the Customer table, you have to make sure that each Customer appears just once in the Customer table.
Hopefully, this helps to tackle your challenge.Regards,
Tom