Date Table Relationship to Multiple Fact Tables

[bacon6actual]

I am still learning powerbi and I have trouble understanding the relationship results when dealing with multiple fact tables and one date deminsion. I work at a police department that runs a software solution for about 20 other local police departments. I am trying to pull crime stats for multiple agencies into visualizations for Offense Reports, Arrest, Calls for Service,...etc... I auto generated a calendar table and I am attempting to connect this date table to multiple fact tables with corresponding dates,... so primary date table connects to offense report dates, arrest dates, and calls for service dates. I also created a union table for all agency codes and connected this one sinlge agency table to each of the fact tables. What I am trying to acheive is visualizations that respect the date filter for all fact tables and displays totals for the fact tables at an agency level. I am doing something wrong because the numbers are not correct. If anyone wants to help me out I would be happy to setup a web session. I will include a picture of my relationship in hoped that this will help. The two columns totals on the left are correct when created from the actual query tables, but if combined based on relationships the data is incorrect as evident in the far right column. 

Thanks in advance from your friends in law enforcement, 

[LaxPBI]

Hi,

I am a newbie to the group and PBI as well.

From Matt's post , it is clear to use multiple look up instances of calendar date for multiple Date fields in 1 Fact Table and that is quite helpful. The issue in my case is a bit extended, where in my model there are multiple fact tables referencing Date tables and they are some how linked through other dimensions. So the question is " If there are multiple fact tables each with atleast 3 - 4 Date Fields , how to establish Date Dim relationships?". Understand , for date fields with in a fact, I can use multiple DAte Dim look up instances, but for multiple facts with multiple Date FIelds and the facts are connected through other Dimensions, what is the best way to establish Date Dim relationships. 

Thanks in advance!

[BhaveshPatel]

Hi there,

You need to set a bidirectional relationships between your tables to correctly slice and dice your data. 

Just to give you a heads up, Relationships alway propogate from one to many direction.

Try setting up bidirectional relationships and see the issue persist.

Thanks & Regards,

Bhavesh

[bacon6actual]

I did this, but it did not help. Ultimately one of the agency relationship becomes inactive. The data is still incorrect. It should match the totals in the single columns to the left. Thanks for the response. 

[BhaveshPatel]

Hi there,

Have you set up a correct data types? 

Regards,

Bhavesh

[bacon6actual]

Yes all data types are set correctly. Its seems pretty staright forward to me, but unlike other BI products its like powerbi does not like to let you filter by more then one deminsion. I do set one relationship to bi-directional the others cause issue and its says that only one filter is allowed. Doesnt make any since to me. I am used to crystal reports and sql. 

[bacon6actual]

Filtering two data tables from one date source should not be this difficult....lol.

[BhaveshPatel · Accepted solution]

Hi There,

Please refer to this blog by Matt Allignton for solving your problem.

http://exceleratorbi.com.au/multiple-relationships-between-tables-in-dax/

Thanks & Regards,

Bhavesh

[bacon6actual]

thank you very much for shedding some light on this. Your help is very much appreciated.

[Barath]

Hi @bacon6actual,

I am too experiencing the same problem:(   By any chance did you get a solution for this problem? If so, can you please let me know.

Thanks in advance.

Regards,

Barath

[anthlen]

I am also having trouble with the same issue

[Barath]

Hi @bacon6actual,

I am too experiencing the same problem:(  By any chance did you get a solution for this issue of yours?

Thanks in advance

Regards,

Barry

[BhaveshPatel]

I am working on to recreate your solution. Meanwhile, if possible, please send us a sample file.

Thanks & Regards,

Bhavesh

[bacon6actual]

I would like to, but unfortunately this is not possible as the data is CJIS protected under FBI regulations. I can only post the problem and perhaps work with someone independently offline if a solution cannot be provided. Thanks for responding.