SUMX expression doesn't hit live query in aggregation data model

[Anonymous]

I currently have an aggregation data model where for 90 days the data should hit an import table, anything greater than that it will go to a live query. 

However, for this query it won't go to a live query.  It just reports back what is in the import table and stops there.  Someone suggested using SummarizedColumns instead but I couldn't get those to work at all. 

Any thoughts as to why?

SUMX(

SUMMARIZE(

FILTER(FACT,FACT[ResourceType] = "Fixed"),

FACT[StopArrivalDate],

"Total Tractors",DISTINCTCOUNT(FACT[TractorID])),[Total Tractors])

Stop Arrival Date	Tractor ID	Resource Type
6/1/2021	1	Fixed
6/1/2021	1	Fixed
6/3/2021	2	Fixed
6/3/2021	3	Fixed
6/3/2021	4	Fixed
6/3/2021	5	Fixed
6/4/2021	6	NOT

[Icey · Accepted solution]

Hi @Anonymous ,

In you scenario, the base data in aggregration table is filtered, not the same as your fact table. And the column you used is contained in the aggregration table. So the result comes directly from the aggregate table, not from the fact table. 

Simply put, the basic data in the fact table and the aggregration table must be the same.  We can't use the filtered fact table as an aggregration table.

Therefore, in your aggregration table, all data should be contained, not just 90 days of data.

I wonder if I explain it clearly.

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.