Facing issue while working with Bridge Table in Power BI

[NiteshkrNishant]

I have created a single unique column from two table, SL Remarks and PM remarks which will act as a bridge table between the two tables. I joined the bridge table with both the table via One to Many relationship.
Now i pull the project code, which is unique, from bridge table and SL remarks from SL Remarks and PM Remarks from PM remarks Table in a single visual, It throws an error "Can't determine the relationship". But at the same time if try to pull in other numerical filed then it shows in aggregation but as soon as i convert it to "Do not summarize the data" again it throws an same error " Can't Determine the relationship".

In short, i am not able to pull the fields from all three tables in a single visual at the same time.

I have been struggling this for a quite long time. Please help me out of this issue.

SL Remarks Table- I have unpivoted the month columns to make it in rows.
PM Remarks Table- It is comprising of 11 PM files and unpivoted the months column in the same way as SL Remarks Table.

[Anonymous]

Hi @NiteshkrNishant,

Thank you for reaching out in Microsoft Community Forum.

Thank you @Laz-MetisBI , @lbendlin  for the helpful response.

As suggested by Laz-MetisBI, Ibendlin, We hope your issue has been resolved. If so, please consider marking it as "Accept as Solution" and giving it a 'Kudos' if it was helpful. This will help other members find it more easily.

Please continue using Microsoft community forum.

Regards,
Pavan.

[Laz-MetisBI · Accepted solution]

I replicated the issue you are having with some dummy rows as you can see below:

Also, see below the error I receive when:

• Project Code ("Bridge") is "Dont Summarize"
• Sales (Table 1) is "Dont Summarize"
• Budget Sale (Table 2) is "Dont Summarize"

I believe this error makes sense. When you select fields from Table 1and Table 2 to “Don’t Summarize,” Power BI tries to display row-level data from two tables that are only indirectly related via the "bridge".

Without a direct row-level relationship between Table 1 and Table 2 tables, there is ambiguity in matching the data, which results in the error.

To resolve this, either use aggregated measures (so that multiple rows are combined into a single value per project code):

Or what I think you will need to do in your case is adjust your data model by merging the tables since row-level detail is essential for you and all the columns you need are descriptive and should not be aggregated.

Updated: You may notice i use bridge in "" above - this is for two reasons. One we should not really be using fields from the bridge in our visuals. They should simply be there to support the propogion from one table to another. Also, both are single direction, in reality in a star schema model one should be both diectional. Hope this helps.

[NiteshkrNishant]

Thanks for your detailes explanation. 

I tried bi-directional also but did not work.
If only aggregation work in case of Bridge table then what could be the best soultion if we have two fact tables(Let say one is Sales table and another is Target Table)?

[Laz-MetisBI]

Setting either of the relationships as bi-directional in your above case wont work for the reason explained.

I recommended you merge tables together, but thats because I did not think you are looking to follow a star schema approach of facts and dims and just wanted a solution to work.

To offer some more thought from my side... If you have two fact tables like you said, Sales and Target, then the table that should be "in-between" them is a Dimension table, not a bridge table. Take the below example I have:

Notice, I have Sales and Footfall. These are two seperate Facts as they have different granularirty. Also, they have common Dimension Tables which are Store and Date.

Of course, I cannot see the details of your entire data model/requirments so I am making some assumptions. However, when we follow a star schena of facts and dims, we should ideally be using descritpive columns in visuals that come from the the Dimension tables, not the fact tables. The fact table should ideally only have numerical columns that can be aggregated or foreign keys that link back to the dimension tables and these should not be used (in most cases) within the visuals. 

Hope this helps.

[NiteshkrNishant]

Great!
You taught me a new concept . I understood your explanation, will follow this method to resolve the issue

[Laz-MetisBI]

Thanks - @NiteshkrNishant!

[lbendlin]

What you did is a dimension table, not a bridge table.  (And you did the right thing!).

Bridge tables need to have at least one of their joins be bidirectional, otherwise they are not bridging anything.

[NiteshkrNishant]

I tried Bi-directional also. But it did not work.

By the way thanks for your reply