Forum Discussion
Facing issue while working with Bridge Table in Power BI
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.
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.
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)?
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.Great!
You taught me a new concept . I understood your explanation, will follow this method to resolve the issueThanks - NiteshkrNishant!