Link Two Tables for visual link

[Miskondukt]

Lo again,

I am using two tables for a variety of display visuals but I cannot link one table directly to another due to no single value in either of the two referenced tables (WklyRawData & Goals). I am sure I am also not setting this up as efficiently as possible as I am having to create a numbe of tables from my raw data to get these to work. 

I would love to have the bubble chart also act like a slicer for all visuals on this first tab, but since the relationship isn't there the bubble only impacts four of the various visuals. 

Any tricks/tips on what I can do to remedy this?

[Anonymous]

Create a locations table, with each location being unique and having your location code.  Link all of those tables to this location table.  Then on your bubble chart, use that location table as the filter.

[Miskondukt]

That 'location's table' is the "Main Companies" table displayed on the relational image and when I attempt what you recommend I get the following:

Am I missing something? All tables are linked via location to that Main Companies table.

[Anonymous]

Check your link between 'Main Companies' and the weekly data. It needs to be a solid line with a 1 to many relationship.  It might even be worth deleting the relationship and relinking it.

[Miskondukt]

@Anonymous Thank you helping guide me in the right direction. I tried that option and got a few more notices from PBID. 

I corrected the relationship to a solid line but cannot change it to 1 to many, says "the cardinality you selected isn't valid for this relationship." Have also tried removing all of the indirect relations and received the same response.

[v-ljerr-msft · Accepted solution]

Hi @Miskondukt,

From your original post, I notice that you have very complex and unclear relationships for your modes.

Normally, we should have one fact table(Sales) with multiple dimensions tables(i.e. Date, Product, Location), then build up relationships around the fact table with multiple dimensions tables, which is often called a Star schema configuration (a central table with several Lookup tables). In this way, it will be much easier to build up visuals that can cross filter each other on the report. 

For more details about how to create and manage relationships in Power BI Desktop, you can refer to this article.

Regards

[Miskondukt]

Got it! It was editing the cross direction filter to "Both" from single.

[Miskondukt]

Correct me if I am wrong, when you state a "fact table" then that is a table of cleaned data that would act/represent that fact table rather than the organic (exported) data?

[Anonymous]

Try restructuring your data relationships such that your "Main Companies" table is in the middle and all other tables (where possible) link directly to it.   Kind of like a star pattern.  The visual interface can help make this easier too.

Some tables won't have a relationship with Main Companies directly, if they don't use the location code and thats fine.  They can link where required to their intended tables.