Struggling to Connect tables for data modeling

[waleedjaved]

I’m working on a Power BI data model where I have 3 fact tables, 2 dimension tables, and 1 date table. The challenge is that some fact tables don’t have direct foreign keys to the dimensions. Because of this, my visuals are not filtering correctly when I apply a Year filter from the Date table. I’m confused about how to correctly connect these tables to build a proper star schema and ensure the filters work as expected.

Here is the link to powerbi file  

[v-agajavelly]

Hi @waleedjaved ,

Thanks a lot to the @Nabha-Ahmed , @danextian and @CranialyticsDev   for jumping in here and sharing the detailed guidance.

@waleedjaved did you get a chance to go through the suggestions already shared? As the super users mentioned, the file link you shared seems to be blank, so it’s hard for the community to validate the exact relationships in your model.

If you’re still facing issues after trying the approaches proper Date table marked, star schema, bridge/lookup tables, or creating derived keys, could you please re-share a workable PBIX file with some sample data? That would make it much easier for the community to give you a precise solution tailored to your model.

Thanks,
Akhil.

[CranialyticsDev]

Hi @waleedjaved 
Just wanted to add a solution.
As you have mentioned few Dimension tables could not be joined with Fact table as there is no direct foreign keys. Below could be possibe solution:
Create Derived Common Column in BOTH table

Conditional Logic/Calculated Columns: If a relationship can be defined by a range or a specific condition (e.g., a salary falls within a min/max range in another table), you might create a calculated column in one or both tables that derives a common key based on that logic
Concatenation: This could be an easy solution to combine values of two of more column to form a new column and use it for relationship. But again if there is complete mismatch in columns then concatenation of columns couldn't be a solution.

I hope this will be helping you to get solution.

[danextian]

The folder you shared is empty.