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,

" 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

    • v-karpurapud's avatar
      v-karpurapud
      Community Support
      10 months ago

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

        Thanks, 

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

        Best,
        Jacek