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:
https://www.youtube.com/watch?v=QlSSdMK5dNI 

Can anybody point me how to properly do this?

Best,
Jacek

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!

10 Replies

  • ABD128's avatar
    ABD128
    Resolver II
    10 months ago

    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'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