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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

Customerordered amount 
A10
A5
B20
B10
C5

Table B

Customeroffered amount
A10
B20
B10

 

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!

1 ACCEPTED SOLUTION

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:
image.png
This allows to create a simple data visualization like the one below:
image.png

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








Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

3 REPLIES 3
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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:
image.png
This allows to create a simple data visualization like the one below:
image.png

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








Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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