There is three tables: City,Calendar,Pdp. Pdp is the fact tables with 20.000.000 rows.
execute the script below and analyze the query plan with profiler
evaluate summarize( 'Pdp', 'Calendar'[year], 'City'[City], "Sales", SUM('Pdp'[Price]) )
the profiler shows that VertiPaq engine get years and cities with a scans the fact table
if i take the year and the city is better to scan the tables of the calendar and the cities than in the fact table because they are smaller. City table contain 1168 row and calendar table contain 1400 row .
rewrite the query using ADDcolumns:
evaluate addcolumns( crossjoin(VALUES('Calendar'[year]), VALUES('City'[City]) ), "Sales", CALCULATE(SUM('Pdp'[Price])) ) the profiler shows that VertiPaq engine get years and cities with scans from both tables (Calendar,City) but don't use fact table.
The Duration is reduced. our fact table has a few lines. But if he had more row the difference between the two techniques would be better.