Forum Discussion

Min_02's avatar
Min_02
Regular Visitor
5 years ago
Solved

Relationships between tables by date

Hi,

 

I have three 3 tables: Sales1, Sales2 and Calendar Table. I need to link Sales1 and Sales2 by product code, and both Sales1 and Sales2 date columns to date column in Calendar Table.

 

The problem I'm facing is that a can create de relationship between Sales1 and Sales2 by product code, but not both date relationships. It's only possible to create one. When I try it, the following message appear: You can't create a direct active relationship between ... and ... because that would introduce ambiguity between tables.

 

Any idea how to link both sales tables to calendar one?

 

Thanks in advanced!

  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    It's inferred from your description that you created a Many to Many relationship between Sale1 and Sale2; it's the last thing in the world of my recommendation even if it's supported by POWER BI! It's at the disposal of data modeling experts. A rule of thumb is to build a One to Many relationship whenever possible.

     

    In your case, I suggest following steps,

    1. extract all unique product code from both Sale1 and Sales to form a dimention table this way,

     

    dim_Product =
DISTINCT (
    UNION ( VALUES ( Sales1[product code] ), VALUES ( Sales[product code] ) )
)

     

    •  Relate dim_Product to Sales1 and Sales2 respectively, POWER BI can detect automatically as One to Many;
    • Now it's supposed to succeed in relating Calendar table to Sales1 and Sales2.

5 Replies

    • Min_02's avatar
      Min_02
      Regular Visitor
      5 years ago

      Hi CNENFRNL , I saw the post you sent me. Based on that issue, in which table should I create de measure? How do you think the measure should be? What are the next steps after creating the measure? Thanks!

      • CNENFRNL's avatar
        CNENFRNL
        Community Champion
        5 years ago

        It's inferred from your description that you created a Many to Many relationship between Sale1 and Sale2; it's the last thing in the world of my recommendation even if it's supported by POWER BI! It's at the disposal of data modeling experts. A rule of thumb is to build a One to Many relationship whenever possible.

         

        In your case, I suggest following steps,

        1. extract all unique product code from both Sale1 and Sales to form a dimention table this way,

         

        dim_Product =
DISTINCT (
    UNION ( VALUES ( Sales1[product code] ), VALUES ( Sales[product code] ) )
)

         

        •  Relate dim_Product to Sales1 and Sales2 respectively, POWER BI can detect automatically as One to Many;
        • Now it's supposed to succeed in relating Calendar table to Sales1 and Sales2.
  • edhans's avatar
    edhans
    Community Champion
    5 years ago

    You need to either create a single Sales Fact table, or have two Sales Fact tables linked only to the calendar table. You should not filter fact tables by each other. These are not SQL joins, but filter relationships.


    You can filter virtually later in measures if you need using TREATAS() or other methods. If you could share some data and what you are trying to do we could be a bit more helpful. The goal though is to get as close to a Star Schema as possible, and those never have FACT tables that are filtered by each other.

     

    Microsoft recommends minimizing use of both Many-to-Many and Bi-Directional Relationships. In other words, unless you are a DAX expert, find another way to remodel your data to conform to a Star Schema and don't use these two features. I avoid them both at all costs.
    Microsoft Guidance on Many-To-Many Relationships
    Microsoft Guidance on Bi-Directional Relationships
    Microsoft Guidance on Importance of Star Schema

     

    How to get good help fast. Help us help you.
    How to Get Your Question Answered Quickly
    How to provide sample data in the Power BI Forum