The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to Solution.
Hey @EduardoLango ,
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
Hey @EduardoLango ,
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 @EduardoLango ,
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
42 | |
30 | |
26 | |
23 |