Relationships

[Anonymous]

Hi 

I'm really stuck on getting the relationships between tables to work. Here are my tables.

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. 

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.

Does anyone have any ideas please? Any advice would be greatly apprecied. 

Thanks in advance!

[Anonymous]

Hi

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]

Thank you so much for your help!!

It works now on "both" 🙂

Much appreciated Walter.

[Anonymous]

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]

Hi

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).

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: https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
How to link multiple Fact tables:

Regards

[Anonymous]

Walter - thank you so mucn for your explanation. 

1) I've followed the Star Schema approach, reduced the both ways joins as much as i beleive i can.

2) I was beating myself up trying to connect Revenue data to the Fact and Dim tables and i no realise that cant work. So ive added in a 2nd Calendar table for Revenue as you suggested and an additoinal mapping table which lets me slice by everything i need. I cant think of an occasion when i would need to use Fact and Revenue data together in one visual - same page sure, but not same visual, so I dont forsee a problem.

I guess my last question would be, is this a good approach/best practice so to speak, both performane wise or logic wise? Would there be any major pitfalls that I'm not seeing by doing this?

Again, thanks so much!

Regards

Ben 

[Anonymous]

Okay so i made some changes. i now have: 

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,

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. 

I'm at a loss and not sure what to do next, i feel like ive tried everything to no avail. 

Any help would be aweome.

Thanks.