Power BI Optimization Playbook: DAX Studio, VertiPaq Analyzer & Cardinality Slashing

[!NOTE] Column cardinality (the number of unique values in a column) is the #1 driver of Power BI memory consumption and slow report rendering.

1. Cardinality Slashing Techniques

1. Split DateTime Columns: A DateTime column with seconds has up to 86,400 unique values per day. Splitting it into a separate Date column and Hour/Minute column slashes cardinality by 99%.
2. Disable 'Auto Date/Time': Under Options -> Data Load, uncheck 'Auto date/time'. This prevents Power BI from generating hidden calendar tables for every datetime column in the model.
3. Remove Unused Columns & Surrogate Keys: Remove GUIDs and raw transaction IDs from reporting models.

Staff Optimization: DAX Studio, VertiPaq Analyzer & Fabric Capacity Smoothing/Throttling

• Measure First With Performance Analyzer + DAX Studio: Never optimize DAX blindly. Capture slow visual queries via Performance Analyzer -> Copy Query, paste into DAX Studio (Server Timings + VertiPaq Analyzer), and look immediately at SE vs FE % (>80% FE = rewrite inefficient DAX iterators/conditions!) and Dictionary Size / Column Size (top 3 columns often eat 90% of RAM!).
• Avoid Calculated Columns on Large Facts: Classic rookie mistake—adding Calculated Column: Profit = FactSales[Rev] - FactSales[Cost] on a 500M-row table forces uncompressed/poorly sorted storage expansion and slows refresh. Compute SUMX or Measure math on the fly ([Total Rev] - [Total Cost]), or push persistent flags upstream to SQL/dbt.
• Visual Overhead (Card Count & High-Cardinality Tables): Limit report pages to <= 6–8 visuals without hidden overlapping objects—every visible chart fires its own async DAX query (30 cards = 30 concurrent CU spikes triggering Fabric Capacity Throttling / Interactive Delay!).