Design based on multiple granularity fct tables and auto scalling date axis

[jaryszek]

Hello,

anybody created a model for havinf multiple granularity fct_tables (daily, monthly) and shared dimensions?

My question is how to make autoscalling x-axis , something similar to this Guy:
https://www.youtube.com/watch?v=QlSSdMK5dNI 

Can anybody point me how to properly do this?

Best,
Jacek

[ABD128]

Hi @jaryszek 

When working with multiple fact tables at different granularities, such as daily and monthly, it is important to model them against a shared date dimension to ensure consistency across all visuals. To achieve an auto-scaling x-axis similar to what you referenced, the best approach is to introduce a dedicated axis table that represents both day- and month-level data. This axis table becomes the driver for the x-axis in your visuals, while measures are designed to dynamically switch between the daily and monthly fact tables depending on the level of detail required. As a result, when a user zooms into the chart, the visual displays daily data, and when they zoom out, it automatically aggregates to monthly data. This design pattern provides a seamless user experience without requiring manual toggling, while also maintaining accuracy and consistency across different levels of granularity. Additionally, Power BI’s support for features like field parameters and aggregations can further enhance this approach by improving usability and performance when working with large datasets.

Regards,

ABD

[jaryszek]

thank you,

can you please share the example how this can look?

Best,
Jacek

[ABD128]

Hi @jaryszek 

This example demonstrates how to structure a Power BI model with multiple fact tables at varying granularities. Both Fact_Sales_Daily and Fact_Sales_Monthly are linked to a central Dim_Date table, which serves as the primary time dimension to ensure consistency across all visuals. Fact_Sales_Daily contains transactional data by date, while Fact_Sales_Monthly provides pre-aggregated monthly figures, connected via the MonthStartDate field.

Within Power BI’s Model View, Dim_Date is centrally positioned with one-to-many relationships to each fact table. An additional Axis_Granularity table, which includes both daily and monthly time points, remains unconnected and is used exclusively for visualization purposes. This table enables auto-scaling on charts: users see daily data when zoomed in and monthly aggregates when zoomed out.

This approach maintains a streamlined model, facilitates accurate filtering across granularities, and enhances the user experience for time-based reporting in Power BI.

Dim_Date sits in the center (the 1-side of both relationships).

Fact_Sales_Daily and Fact_Sales_Monthly are connected on the many-side.

Both fact tables relate to Dim_Date[Date], using Date and MonthStartDate respectively.

Regards,

ABD

[jaryszek]

Thank you,

" An additional Axis_Granularity table, which includes both daily and monthly time points, remains unconnected and is used exclusively for visualization purposes. This table enables auto-scaling on charts: users see daily data when zoomed in and monthly aggregates when zoomed out."

How to enable it using this table? How this table should look like? 

Best,
Jacek

[ABD128]

Hi @jaryszek 

The Axis_Granularity table serves as a unified time axis in the model but doesn’t automatically adjust granularity when zooming. In Power BI, the auto-scaling effect between daily and monthly data is achieved by combining this axis table with a dynamic measure or field parameter that switches between datasets based on the selected granularity.

Both Fact_Sales_Daily and Fact_Sales_Monthly connect to a shared Dim_Date table for consistent filtering, while the Axis_Granularity tablecontaining both daily and monthly time points remains unconnected and is used only in visuals. The dynamic logic references the appropriate fact table depending on the context or user selection, creating the appearance of an automatically scaling chart.

This approach keeps the model clean, supports flexible time-based analysis, and enhances the user experience. To make it fully interactive, you can use field parameters to let users toggle between daily, monthly, or other time levels directly within the visual.

If you’d like to see this implemented visually, please share a small sample of your data. That will help us build a concrete example and provide you with a robust, ready-to-use solution.

Regards,

ABD

[jaryszek]

thank you @ABD128 

I am adding my model:
https://drive.google.com/file/d/16EOv3bVoeVKhIpyb0AqhJnx3CVI_M3yy/view?usp=sharing

thank you in advance!

Best,
Jacek

[v-karpurapud]

Hi @jaryszek 

Thank you for reaching out to the Microsoft Fabric Community Forum.

We have reviewed your data model and found it well-suited for managing multiple fact tables at different granularities. Both the fct_amortizedcosts_daily and fct_amortized_monthly tables are properly connected to the shared dim_date table, which supports consistent filtering and alignment in your visuals.

While Power BI does not offer automatic granularity adjustment based on zoom, you can accomplish this by implementing a granularity or parameter table in combination with a dynamic measure. The granularity table or a Power BI field parameter can facilitate day or month selection, and the dynamic measure can reference the relevant fact table. This method allows a single visual to transition smoothly between daily and monthly data, effectively replicating the auto-scaling functionality you described.

I hope this information is helpful. . If you have any further questions, please let us know. we can assist you further.

Regards,

Microsoft Fabric Community Support Team.

[jaryszek]

Thanks, 

Can you please show the example how this model can look like?

Best,
Jacek

[v-karpurapud · Accepted solution]

Hi @jaryszek 

I reproduced the scenario on my end using sample data and it worked successfully. To help you better understand the implementation, I’ve attached the .pbix file for your reference. Please take a look at it and let me know your observations.

Thank you for being part of the Microsoft Fabric Community!

[jaryszek]

Thank you I will implemented something similar in my model.

Best,
Jacek