Forum Discussion

bacon6actual's avatar
bacon6actual
Frequent Visitor
9 years ago
Solved

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, 

 

 

 

13 Replies

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    9 years ago

    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's avatar
      bacon6actual
      Frequent Visitor
      9 years ago

      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's avatar
        BhaveshPatel
        Super User
        9 years ago

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

         

         

        Thanks & Regards,

        Bhavesh

  • LaxPBI's avatar
    LaxPBI
    Frequent Visitor
    9 years ago

    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!