Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Difficulty with relationships creation

 Hello friends, 

 

 I have a difficulty creating relationships in Power BI. I thought I created them properly, but when I create visualizations i clearly understand smth is wrong. 

I will greatly appreciate if someone may give me an idea where I am wrong. 

I have 5 tables:

1. Last Ship Date - unique list of outbound itme and last ship date. (unique key)

2. Locations/Dimensions - locations and list of items in them (key is not unique, some key values are repeated)

3. Move Type - is in a sense a central table. It is a list of items that need to be moved. Unique key.

4. MAX Last Process Date -list of items and date when it was last processed. Unique key.

5. Title/Publisher/WAC/MSRP - Just a list of items with their description. Only table where i don't have a key. It is just a description of what the item is about. No measures, calculations nothing here. 

 

 

Can someone (who has more experience) in Power BI let me know if I connected smth not properly?

 

All the tables (with the exception on Title/Publisher/Type are connected on key. Title/Publisher/Type connected to all others on Item field.

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Maybe someone knows?

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi Anonymous 

     

    In Power BI, relationships don't always need to be created on the Key columns. They can also be created on other columns. And you have several inactive relationships (dotted lines) in your model. These inactive relationships will not work when you drag columns directly into visuals. Only active relationship (solid lines) will be used to filter data according to their cross-filter directions. To use an inactive relationship, you need to use USERELATIONSHIP function in DAX expressions to activate an inactive relationship.

     

    When you understand something is wrong, it is probably because the active relationship is not chosen or set up correctly. Which column are used in the visual? What is the active relationship between the two tables they are from? Are the columns in the relationship correct that you hope to lookup values based on these columns? When columns are correct, check the cross-filter directions. If tableA should filter tableB, it must have a relationship whose cross-filter direction points to tableB. 

     

    As there could only be one active relationship between two tables, if you want to use two relationships at the same time, you could use USERELATIONSHIP to create a measure to get the result. 

     

    These articles may be helpful:

    Beginner’s Guide to Relationships in Power BI – Curbal

    Create and manage relationships in Power BI Desktop - Power BI | Microsoft Docs

    Model relationships in Power BI Desktop - Power BI | Microsoft Docs

     

    Best Regards,
    Community Support Team _ Jing
    If this post helps, please Accept it as Solution to help other members find it.