Forum Discussion

jaryszek's avatar
jaryszek
Super User
10 months ago
Solved

Manage aggregations multiple fact tables DirectLake over OneLake

Hi Guys,

I want to use multiple fct_tables granularity like fct_table_daily, fct_table_monthly etc.

I am using DirectLake over OneLake remote semantic model. 

1. It is feasible to use this to autmatically fetch proper data from slicer? 
For example If user will select Month, visual will fetch data from  fct_table_monthly table? 

2. If it making sense to switch to aggregated tables using import mode over using direct lake tables directly and just switch visuals/measured based on selection? 
When to decide to choose one or second approach? 

Best Wishes,
Jacek

Need Help
  • anilelmastasi's avatar
    anilelmastasi
    10 months ago

    Hello Jacek,

     

    In aggregatinos tables, the user cannot force which table is used. If they select “Month” in a slicer, the engine may still query the daily table if the visual structure doesn’t match the aggregation definition.

    Aggregations are designed for performance optimization, not user-driven logic. But in calculation groups there is explicit user control, the slicer dictates exactly which table is queried.

     

    Best,

    Anıl

5 Replies

  • anilelmastasi's avatar
    anilelmastasi
    Super User
    10 months ago

    Hello jaryszek ,

     

    You can create a composite  model with calculation groups. For this, you need Tabular Editor (or another TOM/ALM-based tool) to add a calculation group into your model. 

     

    After that you can create a disconnected parameter table:

    Granularity = DATATABLE(
    "Level", STRING,
    {
        {"Daily"},
        {"Monthly"}
    }
)

    And then  you should define a calculation group like "Granularity Selection" with a calculation item like:

    Selected Value =
SWITCH(
    SELECTEDVALUE(Granularity[Level]),
    "Daily", CALCULATE(SUM(fct_table_daily[Amount])),
    "Monthly", CALCULATE(SUM(fct_table_monthly[Amount]))
)

     

    If this solved your issue, please mark it as the accepted solution. ✅ 

    • jaryszek's avatar
      jaryszek
      Super User
      10 months ago

      Hello,

      why this is better than Aggregations tables?

      Best,
      Jacek

      • anilelmastasi's avatar
        anilelmastasi
        Super User
        10 months ago

        Hello Jacek,

         

        In aggregatinos tables, the user cannot force which table is used. If they select “Month” in a slicer, the engine may still query the daily table if the visual structure doesn’t match the aggregation definition.

        Aggregations are designed for performance optimization, not user-driven logic. But in calculation groups there is explicit user control, the slicer dictates exactly which table is queried.

         

        Best,

        Anıl