Multiple relations between one Dimension table and one Fact table

Question

I have only recently been learning Power BI and now I tried to make a star model (1 fact table and 1 dimension table), since that is recommended.

My practice data contains the following.

Dimension table: Company name, Company ID, Company manager

Fact table: Selling company ID, Buying company ID, Date, Price

I thought i would set up relations between 'Company ID' and 'Selling company ID' and between 'Company ID' and 'Buying company ID'. The 'Company ID' in the Dimension table is unique and the ID's in the Fact table are not. So i wanted to make 1:many relationships from Dimension to Fact table.

I believed this would give me the possibility to see which company sold which product to which company. But Power BI does not allow me to make multiple active relationships between those two tables.

Did I model my data wrong or is there a solution? Should I make a second Dimension table? One for buyers, one for sellers?

Agustin Palacios Agustin Palacios · Accepted Answer · 2020-07-31T19:50:58

Only one active relationship can be existing between two tables. There are some ways to work with multiple relationships but its a bit complicated. However you can modelate your fact table in order to solve this problem using the following dax code:

Company Fac Table = 
VAR __buyingTable = FILTER( 'Fact Table', NOT( ISBLANK( 'Fact Table'[Buying company ID] ) ) )
VAR __sellingTable = FILTER( 'Fact Table', NOT( ISBLANK( 'Fact Table'[Selling company ID] ) ) )
Return
UNION (
    SELECTCOLUMNS (
        __buyingTable,
        "Company ID", 'Fact Table'[Buying company ID],
        "Date", 'Fact Table'[Date],
        "Price", 'Fact Table'[Price], 
        "Company type", "Buying Company"
    ),
    SELECTCOLUMNS (
        __sellingTable,
        "Company ID", 'Fact Table'[Selling company ID],
        "Date", 'Fact Table'[Date],
        "Price", 'Fact Table'[Price], 
        "Company type", "Selling Company"
    )
)

The idea is to create a large table having only 1 Company ID and a column Company Type to difference betweewn both selling and buying company.

So your final model would be something like this:

You can achieve this using power query but this is the fastest way.

Multiple relations between one Dimension table and one Fact table

2 Answers