Multiple relations in table

[Meena0155]

Hi all,

I was hoping one of you can guide me as to how I can best design the table structure need to support the following requirement.

I have a requirement where I can filter the fact tables based on predefined date periods. For eg, I have a fact table (you can see sample data in the attached spreadsheet) that has the following columns

1. Date Period - daily, weekly, monthly, quarterly
2. Date Start - Start date for the date period selected
3. Division - A dimensions by which we need to slice the data
4. Accounts - a metric showing the number of accounts in the division

The combination of Date Period and date Start can be used to select the reporting date range for the dashboard. For eg, if the user chooses a date period of weekly and date start of ‘’Jan 1 , 2025’, then all the KPI cards should show the data for the period from Jan 1 to Jan 7 , 2025. Similarly, if the user chooses date period of monthly and date start of ‘’Jan 1 , 2025’, then all the KPI cards should show the data for the month of Jan , 2025 and so on.

I have created a master date table called dim_date (you can see sample data in the attached spreadsheet) which has date period and date start columns and have defined a 1 to many relation between dim_date and fact table. This work beautifully for all KPI cards.

However I need to add a trend chart where if the user chooses the date period of weekly and date start of ‘’Jan 1 , 2025’, then we need to show the metrics for each date within the period. Similarly, if the user chooses a quarter, then we need to show metrics aggregated to each month within the quarter.

This requirement can’t be addressed using the current relation. I thought of creating a middle table(you can see sample data in the attached spreadsheet) to address this issue and trying to create a relation between dim_table to middle table to fact table. However since there is already an active relation between fact table and dime_date table, this would not work.

Any idea what can be a potential solution here. can we use dax to solve this? If possible, I would like to avoid creating a duplicate table just to support the line chart. Please help. Any idea is appreciated

[Anonymous]

Hi @Meena0155 ,

Thanks for reaching out to us.

To address your requirement, it is suggested to creating a bridge table that maps each date period and start date to the individual dates within that period. Using DAX measures, you can dynamically filter and aggregate data, allowing the trend chart to display metrics for each date within the selected period.

This approach avoids duplicating tables and keeps the data model efficient.

If you still have any doubts, it would be best to provide some sample data and the expected results. This will help us understand your requirements more clearly and offer a more precise solution.

Best Regards,
Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly