Relationships

Question

Hi&nbsp;&nbsp;I'm really stuck on getting the relationships between tables to work. Here are my tables.&nbsp;The Calendar (months) doesnt seem to be working when i use the "count" measure in DimPlayer table which means that join isnt working, and same for Table. But im not sure how else to join this, as i dont have anymore related fields.&nbsp;&nbsp;The Dim Tables (standard as per BI) connect and work fine i beleive, its when i pull it by month that i get the total number each month, which means the join isnt working.&nbsp;Does anyone have any ideas please? Any advice would be greatly apprecied.&nbsp;&nbsp;Thanks in advance!&nbsp;&nbsp;

anonymous · Answer

Hi&nbsp;DimPlayer cant work with Calendar because filter direction dont allow it. In your case if you choose date for calendar is only work for values in Fact and doesnt for Dim. You can change filter direction to both, all is there:https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships

anonymous · Answer

Thank you so much for your help!!&nbsp;It works now on "both" :)&nbsp;Much appreciated Walter.

anonymous · Answer

Actually, i spoke to soon. It solved some problems, players by table seems to work (Fact and DimTable).

But i'm still getting repeated numbers when pulling from revenue by table, using dimtable and calander month.

Should they all be set to "both" like so?

Thanks!

anonymous · Answer

Okay so i made some changes. i now have:&nbsp;&nbsp;1) Many to Many, both ways on each dim table, fact and calander. this seems to work fine with any data in the Fact Table. So thanks for that, that was an interesting read in the link above. However,&nbsp;2) I'm still unsure how to connect the Revenue table correctly, as this data doesnt come through (same number each month). I need this to work as if it were the FactTable.&nbsp;&nbsp;I'm at a loss and not sure what to do next, i feel like ive tried everything to no avail.&nbsp;&nbsp;Any help would be aweome.&nbsp;Thanks.&nbsp;

anonymous · Answer

Hi&nbsp;First of all, "Both" can make report slower. Remember about performance, use it only if you have to.Next, you cannot use them in closed model cause it disable on of relationship. In your case from previous post it disable "Revenue data" with "Calendar", cause they dont know which way to filter use: by "Fact" or "Revenue data"?Your model is closed, if you use star schema you dont connect Dim tables between itself. In your case you can make all relations to Calnedar one-direction or do Calendar table to each table you need (one for Fact and second for Revenue data).&nbsp;BTW: DimPlayers have cardinality one-to-many, so one Player can have many values in Fact table. Many rows mean many dates from Calendar for one Player.More about star schema:&nbsp;https://docs.microsoft.com/en-us/power-bi/guidance/star-schemaHow to link multiple Fact tables:Regards

Forum Discussion

Relationships

6 Replies

Related content

Relationship

Relationships

Relationship Issue

Creating relationships

PowerBI Relationships