Search code examples
sqlpowerbidaxdatamodel

Creating Relationship in PowerBI

I have to create a relationship, where both table have dates and measures. I created name bridge between two tables. if i pull by name it works, however, not with date, i am not able to create the relationship. Can you please help to create relation between dates, so that amount can be displayed by Name and date for amt_outstanding and Amt_received together. Below is the sample and outcome sample.

Thanks in Advance

Sample Screenshot

Solution

  • You can have only one active relationship between two tables. So, if you are already having active relationship on Name, then the date based relationship will be inactive relationship. So, it is not working.

    Btw, your model has to go for below changes:

    1. Create a Date Dimension with the list of dates
    2. Now, create relationship between individual fact tables and corresponding date dimension. Preferably, try to have a single Fact table with Amount_Recevied, Amount_Outstanding, as it will make the model much cleaner.

    Now, if you are dragging and dropping the fields, they will work fine without issues.