Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • TomMartens's avatar
    TomMartens
    4 years ago

    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






TomMartens's avatar
TomMartens
Super User
4 years ago

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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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!

  • TomMartens's avatar
    TomMartens
    Super User
    4 years ago

    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