7. A Good Model: Star Schema, Measures, and Speed

The Power Pivot model compresses data column by column. Speed and size depend on how the model is built more than on the formulas.

• The star: one fact table (narrow and long) with lookup tables around it. Don't glue everything into one wide table;
• remove unneeded columns already in Power Query: every column with unique values (an invoice number, a time down to the second) bloats the file;
• prefer measures to calculated columns: a column is stored in the model for every row, a measure is computed only for the report;
• a simple condition in CALCULATE (Products[Category] = "Beverages") is faster than FILTER(Sales, …) over the whole fact table;
• relate tables on integers or short codes, and set the filter direction from the lookup table to the facts;
• a separate date table with every day in a row, marked as the date table, is a requirement for time intelligence functions.