27.3 Reduce Model Size and Cardinality
In short: The model file is huge because of a DateTime column with unique seconds and unused columns.
Start with a problem
The model file is huge because of a DateTime column with unique seconds and unused columns.
Explain simply
VertiPaq compresses columns; cardinality (number of unique values) drives size. Remove unused columns, split DateTime, lower precision.
Show the data
| Column | Unique values (sample) | Action |
|---|---|---|
| Order DateTime | 8 (one per order) | Split to Date + Hour |
| Order ID | 8 | Keep if needed |
| Notes text | 8 | Remove |
About this data: This table uses columns: Column, Unique values (sample), Action. This table uses columns: Change, Effect. This table uses columns: Column, Different values. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: visuals_orders_apr_may_2026.csv (source: src/practice/).
Demonstrate
- Open DAX Studio › View Metrics (VertiPaq Analyzer).
- Sort columns by size.
- In Power Query remove unused columns.
- Split DateTime into Date and Time; round time if possible.
- Refresh and compare size.
Show the result
Your result should look like this (fictional sample):
| Change | Effect |
|---|---|
| Remove Notes | Smaller model |
| Split DateTime | Fewer unique values per column |
Explain what happened
Fewer unique values mean better compression.
Explore further
- Disable Auto date/time for large models.
Detailed reference for this lesson:
Reduce cardinality in Power BI with example — in 30 seconds
What: Cardinality is the number of different values in a column. Fewer different values make a smaller, faster model.
Why: Order DateTime with seconds has a new value on almost every row. It makes the file big and slow.
Tiny example: Split Order DateTime into Order Date and Order Time (to the minute). One year has only 365 dates and 1,440 minutes.
Reduce cardinality in Power BI with example — 30 सेकंदात
काय: Cardinality म्हणजे एका column मधल्या वेगवेगळ्या values ची संख्या. कमी वेगळ्या values म्हणजे लहान, fast model.
का: Seconds सकट Order DateTime ची जवळजवळ प्रत्येक row वर नवीन value असते. त्यामुळे file मोठी आणि slow होते.
छोटं उदाहरण: Order DateTime चे Order Date आणि Order Time (मिनिटापर्यंत) असे दोन करा. एका वर्षात फक्त 365 dates आणि 1,440 मिनिटं असतात.
Reduce cardinality in Power BI with example — 30 सेकंड में
क्या: Cardinality यानी एक column में अलग-अलग values की संख्या. कम अलग values यानी छोटा, fast model.
क्यों: Seconds के साथ Order DateTime की लगभग हर row पर नई value होती है. इससे file बड़ी और slow होती है.
छोटा उदाहरण: Order DateTime को Order Date और Order Time (मिनट तक) में बाँटिए. एक साल में सिर्फ़ 365 dates और 1,440 मिनट होते हैं.
First look at the data (before → after):
Same before / after tables as text
Before — Orders — one DateTime column (fictional: 1 year, 5 lakh rows)
| Column | Different values |
|---|---|
| Order DateTime (with seconds) | about 4,96,000 |
| Order ID | 5,00,000 |
Formula / click path
Add Column › Date › Date Only and Add Column › Time › Time Only
Remove seconds, then remove the old Order DateTime column
After — After the split
| Column | Different values |
|---|---|
| Order Date | 365 |
| Order Time (to the minute) | 1,440 |
| Order ID | 5,00,000 (kept: Total Orders needs it) |
Do it step by step:
- In Power Query, select Order DateTime.
- Click Add Column › Date › Date Only. Rename it Order Date.
- Select Order DateTime again. Click Add Column › Time › Time Only. Rename it Order Time.
- Remove the seconds with a custom column:
#time(Time.Hour([Order Time]), Time.Minute([Order Time]), 0). - Remove the old Order DateTime column. Click Close & Apply.
- Save the file. Compare the .pbix size before and after.
एक-एक step करून बघा:
- Power Query मध्ये Order DateTime निवडा.
- Add Column › Date › Date Only वर click करा. नाव Order Date करा.
- पुन्हा Order DateTime निवडा. Add Column › Time › Time Only वर click करा. नाव Order Time करा.
- Custom column ने seconds काढा:
#time(Time.Hour([Order Time]), Time.Minute([Order Time]), 0). - जुना Order DateTime column काढून टाका. Close & Apply वर click करा.
- File save करा. आधीची आणि नंतरची .pbix size compare करा.
एक-एक step करके देखिए:
- Power Query में Order DateTime चुनिए.
- Add Column › Date › Date Only पर click कीजिए. नाम Order Date रखिए.
- फिर Order DateTime चुनिए. Add Column › Time › Time Only पर click कीजिए. नाम Order Time रखिए.
- Custom column से seconds हटाइए:
#time(Time.Hour([Order Time]), Time.Minute([Order Time]), 0). - पुराना Order DateTime column हटाइए. Close & Apply पर click कीजिए.
- File save कीजिए. पहले और बाद की .pbix size की तुलना कीजिए.
Power BI's VertiPaq engine stores data by column and compresses it. Compression (संक्षेपण) is best when a column has few distinct values (low cardinality). So:
- Remove unused columns – the biggest win. Every column costs memory, even if nobody uses it.
- Remove unnecessary rows – e.g. load only the last 3–5 years if older history is not needed.
- Split Date/Time columns into a Date column and a Time column (or drop the time if not needed). A timestamp with seconds (like
Order DateTime) can have millions of unique values; a date column has a few thousand, and an hour column only 24. - Round decimals to the precision you need (e.g. 2 decimal places).
- Avoid high-cardinality text columns such as comments, GUIDs or unique transaction descriptions unless really needed.
- Use integer keys instead of long text keys where possible.
- Turn off Auto date/time.
- Prefer calculated columns in Power Query or the source rather than DAX calculated columns (they compress better and keep the model simpler).
- Use summarised tables or aggregations (एकत्रीकरण) for very large fact tables.
Ravindra Bagale's Tip
Friends, many students load DateTime columns with seconds, long text comments and unused ID columns, and the model balloons. Split DateTime into Date and Time, remove columns nobody uses, and reduce cardinality. VertiPaq Analyzer in DAX Studio shows which columns take the most space. Remember this rule.
Ravindra Bagale's Tip – मराठी
मित्रांनो, बरेच students seconds सकट DateTime columns, मोठ्या text comments आणि न वापरलेले ID columns load करतात, आणि model फुगतं. DateTime चे Date आणि Time मध्ये तुकडे करा, कोणी वापरत नाही असे columns काढा, आणि cardinality कमी करा. DAX Studio मधला VertiPaq Analyzer कोणते columns सगळ्यात जास्त जागा घेतात ते दाखवतो. हा नियम लक्षात ठेवा.
Ravindra Bagale's Tip – हिंदी
दोस्तों, बहुत से students seconds वाले DateTime columns, लंबे text comments और बेकार ID columns load कर देते हैं, और model फूल जाता है. DateTime को Date और Time में बाँटो, जो columns कोई इस्तेमाल नहीं करता उन्हें हटाओ, और cardinality कम करो. DAX Studio का VertiPaq Analyzer दिखाता है कि कौन से columns सबसे ज़्यादा जगह लेते हैं. यह नियम याद रखो.
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| File still big | Removed rows not columns | Remove high-cardinality columns |
| Broken visuals | Removed used column | Check usage first |
| Keeping ID columns as text | Text larger | Use integers |
Practise independently
Practice task
Which column has the lowest cardinality: City or Order ID?
Hints
- Count unique.
Solution
City (3 vs 8).
Think like a professional
Cardinality is the key word in model optimisation interviews.
Revision card