Hitting Agg Table with Disconnected Table

[dd2909]

Dear experts,

I am using a direct query connection to a fact table that has 5 dimensions.

I have successfully set up an aggregation table. The aggregation table sums the output of the fact table and is related to the dimensions directly. Using DAX studio, I can see I hit the aggregation table successfully for simple queries.

In the report, I use a matrix to return values from the fact table.

I am using a disconnected table and a measure to populate the matrix. The matrix uses the period names from the disconnected table as the column header and the measure are the values. The rows in the matrix are dimensions.

Measure to populate the matrix (edited to one period – the actual measure has several periods)

EDU_CALC_MATRIX =

//Save Selected Value from Matrix

VAR _SelVal = SELECTEDVALUE( EDU_Matrix[Period_Name] )

//Lookup Period Names

VAR CY_CE = LOOKUPVALUE(EDU_Matrix[Period_Name], EDU_Matrix[Period_Code], "CY_CE")

//Calculate Period Values

VAR _CY_CE = CALCULATE( [eduOutput], tblEDU_Periods[Period_Code] = "CY_CE")

RETURN

SWITCH( _SelVal,

       CY_CE, _CY_CE

)

The disconnected table allows for the user to use a slicer to add or subtract periods and add variance columns. The variance calculations (e.g. This Year vs Last Year, Q3 estimate vs budget) are not in the fact table. It also turns periods into relative references, so I do not have to update the label of the measure in the matrix (I change the table that lists the name of “CY_CE” to “2024 Current Estimate”). I tried using individual measures for each period (and it hit the agg table because it does not use the disconnected table), but this would require report-level maintenance and also may not allow users to add/subtract periods on demand.

In performance analyzer and DAX Studio, I can see queries that use the measure referring to the disconnected table for variance calculations do not hit the aggregation table.

In this case, the LOOKUP part of the measure for the period name does not hit the aggregation table, while the CALCULATE part does, I think.

Disconnected table misses (DAX studio, server timings):

Fact table hits agg:

This result makes sense because the disconnected table is not connected to the model, but is there a work around?

Is there a way to use a disconnected table for the measures and also hit the aggregation table?

I am not sure I can add the variance calculations to the fact table, given the variance calculation depends on the row context in the matrix.

Any thoughts? I appreciate your advice.

Thanks,

David

[Anonymous]

Hi @dd2909 ,

The documentation describes the Agg table model with non-dependent relations:
User-defined aggregations - Aggregation based on GroupBy columns

And try rewriting the LOOKUPVALUE() part of the code using TREATAS() to see if you can hit the Agg table.
TREATAS – DAX Guide
Propagating filters using TREATAS in DAX - SQLBI

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group