Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
bacon6actual
Frequent Visitor

Date Table Relationship to Multiple Fact Tables

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, 

 

Screen Shot 2016-09-06 at 4.06.19 PM.pngScreen Shot 2016-09-06 at 4.04.38 PM.png

 

 

1 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

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

View solution in original post

13 REPLIES 13
LaxPBI
Frequent Visitor

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
Community Champion
Community Champion

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

 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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. 

Hi there,

 

Have you set up a correct data types? 

 

Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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. 

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

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

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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

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

I am also having trouble with the same issue

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

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

 

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

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. 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.