Manage aggregations multiple fact tables DirectLake over OneLake

[jaryszek]

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

[anilelmastasi]

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]

Hello,

why this is better than Aggregations tables?

Best,
Jacek

[anilelmastasi · Accepted solution]

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

[jaryszek]

thank you. 

This is great answer!

[anilelmastasi]

You are welcome Jacek, could you please accept as solution 🙂