Forum Discussion

jaryszek's avatar
jaryszek
Super User
10 months ago
Solved

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

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:...
Need Help
  • v-karpurapud's avatar
    v-karpurapud
    10 months ago

    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's avatar
jaryszek
Super User
10 months ago

thank you,

can you please share the example how this can look?

Best,
Jacek

ABD128's avatar
ABD128
Resolver II
10 months ago

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's avatar
    jaryszek
    Super User
    10 months ago

    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's avatar
      ABD128
      Resolver II
      10 months ago

      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