When it comes to Power BI performance, most developers focus on DAX optimization and visual simplifications, but one of the biggest bottlenecks is often hidden in the data model itself—high-cardinality columns.
If your reports are slow, consuming too much memory, or taking forever to refresh, chances are your cardinality is too high. Let’s break this down in a simple and informative way.
What is Cardinality?
Cardinality refers to the number of records in a column.
1.Low Cardinality = Fewer unique values → Better performance
2.High Cardinality = Many unique values → Slow reports & high memory usage
Example:
Why does this matter?
Power BI compresses data for faster performance. But high-cardinality columns don’t compress well, which leads to:
-Longer refresh times
-High RAM usage
-Slow visuals
How to Reduce High Cardinality & Improve Performance?
1. Remove Unnecessary Columns
Many datasets come with extra columns that aren’t needed for reporting.
Example:
Keeping Order ID (1 million unique values) in the report.
Solution:
Remove it unless needed for specific analysis.
Impact:
-Reduces dataset size
-Improves Power BI’s compression efficiency
2. Replace Text Columns with IDs
-Why? Text fields take more memory than numeric fields in Power BI’s VertiPaq storage engine.
Example: Storing Customer Name in the Fact Table
Problem:
-Each name is unique → High cardinality
-Takes more memory & slows down queries.
Solution:
Replace names with Customer IDs and store names in a Lookup Table.
Impact:
-Better compression.
-Faster filtering and grouping in reports.
3. Aggregate Large Fact Tables
Instead of storing every transaction, group data into a summary format when possible.
Example:
Storing daily sales transactions for 5 years → Millions of rows!
Solution:
Aggregate data monthly instead of daily.
Before Optimization: (High Cardinality)
After Optimization: (Low Cardinality)
Impact:
Fewer rows → Faster queries → Faster reports
4. Remove Unnecessary Decimal Precision
-Why? Storing numbers with many decimal places increases storage size.
Example:
Sales stored as 1024.5678
Solution:
Store as whole numbers (e.g., 102457 cents)
Impact:
-Saves memory
-Reduces processing time
5. Optimize Date Tables
Many datasets store timestamps (date + time) when only dates are needed.
Example:
Problem:
-Every unique timestamp increases cardinality
-Slows down filtering and relationships
Solution: Store only dates if time isn’t needed.
Impact:
-Less memory usage
-Better compression.
Performance Boost After Optimization (Before vs. After)
Key Takeaway:
Lowering cardinality dramatically improves Power BI performance!
Conclusion
-High-cardinality columns slow down reports, increase memory usage, and make DAX calculations inefficient.
-Removing unnecessary columns, aggregating data, and using lookup tables significantly improves Power BI performance.
-A well-optimized model leads to faster visuals, smoother interactions, and better scalability.