Forum Discussion

KitCat_PBI's avatar
KitCat_PBI
Regular Visitor
3 years ago

Power BI Relationship issues

Hi all,

 

­­­I need some guidance around PowerBI as I have not been able to solve my issue using Google or the queries others have - I am fairly new to PBI so I am not sure if I undertsand some of the answers that have been provided. 

 

The issue I have is around relationships and filtering.

 

I have multiple tables I need to filter by both date and directory, which is fine. The difficulty is I need to also filter the directory by the date to ensure only colleagues in the relevant teams on the relevant date being looked at show, but it needs to reflect correctly going back to the start of the year and forwards as changes are made. I have tried to map this multiple times and keep facing ambiguity errors with the date table. If I filter the directory I can’t filter the rest of the data by date so it shows the incorrect volumes, if I filter the other tables then colleagues who are no longer in the relevant teams are reporting data that affects the team totals.

 

My current relationship map shows as below. Please you can point me in the right direction to enable me to complete this as required, or advise if there is another route I can take to end with the same outcome?

 

I have also tried:

I have also tried to filter everything via the directory, after filtering the directory by date table and tried to create a second date table to bridge the filters, but I am not having any success. 

 

Date Table is marked as a Date Table and was created using Calendar, in case this makes a difference. 

 

Any help would be very much appreciated as I am not sure what I can do to make this a success. 

 

Thansk

Cat

Need Help

3 Replies

  • aj1973's avatar
    aj1973
    Community Champion
    3 years ago

    Hi KitCat_PBI 

    From a glance, your model contains too many relationships with Many To Many, not good.

    What I would recommend you is start building your model step by step depending on your business needs. So, I would start by deleting all the relationships, open a new Tab and bring only the tables that you need for your KPIs or visuals, create a 1 too many relationship and if dosen't work then look for the Table that could make the bridge.  

    • KitCat_PBI's avatar
      KitCat_PBI
      Regular Visitor
      3 years ago

      Hi aj1973, 

       

      I have tried rebuilding this model a few times and tried to reduce the many to many relationships, however due to the nature of the business and the data tables this has not really been possible to reduce. The only tables in the model are the ones that are being used for the visuals.

       

      I have tried to create a bridging table between the dates and the directory but no matter what I try I have no success. Is there anything else I can try?

       

      Thanks
      KitCat85

      • aj1973's avatar
        aj1973
        Community Champion
        3 years ago

        Hi KitCat_PBI 

        Honestly it is very hard to tell you what to do just by looking at some pictures. Maybe a mock pbix can help me understand your problem