Multiple Inactive Relationships Between Tables

[PowerBI-Newbie]

Hi,

I have the following 4 tables:

1. Timesheet_Data
2. RCP_Demand_Data
3. RCP_Allocation_Data
4. Complete_Calendar

The relationship is as follows:

I know the limitations of Power BI in terms of being able to only have 1 active relationship between 2 tables so as you can see I have several inactive ones between RCP_Demand_Master and RCP_Allocation_Master and I searched for examples on this forum and other places online but none of them worked for me.

The RCP team enter demand for a piece of work/activity and the associated info to go with it and that gets allocated a Demand ID (or also called Demand Forecasted ID). When someone is available to do the work then they get allocated to that Demand ID and that person's details goes into the RCP_Allocation database. Every period, an updated snapshot is sent out to everyone and we need to know what's changed, what's missing, etc. and that's why there's the Tool Date & Time column.

At the moment I don't know how I can display the correct values for Demand (Hours) and Allocation (Hours) - when the user selects the value from R&CP Tool Date & Time slicer, a breakdown should be provided of the sum of Demand (Hours) and Allocation (Hours) based on Demand ID and Year:Period columns (and any subsequent info required). I can get it if I was doing it individually but not when the tables are combined (see picture below):

Individual:

Combined:

Here's the link to the .pbix file (I've removed all sensitive information):

https://drive.google.com/file/d/1seR7gzWXjbYNZnFsquIn9tulR8fEr_jS/view?usp=sharing

It has 3 pages:

1. Individual - this has the correct values that need to be displayed
2. Combined - summary of both values; correct for Demand (Hours) but not Allocation (Hours)
3. Breakdown - breakdown of Demand (Hours) and Allocation (Hours); correct for the former but not for the latter

I want to be able to do further advanced analysis but I'm stuck at this stage. Your help is greatly appreciated.

[aj1973]

Hi @PowerBI-Newbie 

As "mentioned" by @Anonymous your model is just a mess! but maybe because it is Sample Pibx that we see the many to many and bi-directional relationship. Modeling your dataset is the second biggest step after Power query and ETL that needs to be taking care of very carefully and understand it in order to make a solid report and performed visuals.

Here is the closest i could get out of your model eventhough it is still not correct:

May be adding a Calendar table to link Demand and Allocation tables through RC date and then use it as a slicer could get you what you need. Good luck

[Anonymous]

@PowerBI-Newbie 

Before you start doing anything in PBI it would be good if you acquired some basic knowledge about the tool. Please read this first: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

Then get rid of all the two-way filtering if you want to stay sane and know what your model tries to calculate. Also, you have to get rid of all the many-to-many relationships. You should also read this: Model relationships in Power BI Desktop - Power BI | Microsoft Docs. And just do not use many-to-many if you don't know exactly when and why they should be used. This is a very good piece of advice.

Please read the article above and make a good star-schema model with dimensions and fact tables with one-way filtering. Then and only then will you be able to create correct and simple DAX.