How to Reduce Cardinality in Power BI

Nothing kills the joy of using Power BI like a report that’s slow, sluggish, and takes forever to refresh. If your dashboards feel like they're wading through molasses, the problem might be "high cardinality." This article breaks down what cardinality is, why it slows your reports down, and shows you several practical ways to fix it.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Exactly is Cardinality in Power BI?

In simple terms, cardinality refers to the number of unique values in a column. A column with low cardinality has very few unique values, while a column with high cardinality has many.

Think about a column for "Country" in a customer table. Even with a large customer base spread across the globe, you might only have about 200 unique countries. This is considered low cardinality.

Now, consider a column for "Customer Email Address." Every single value is likely unique. If you have 500,000 customers, you'll have 500,000 unique values. This is an example of extremely high cardinality. Other common culprits include transaction IDs, date/time stamps (down to the second or millisecond), GPS coordinates, or free-text survey responses.

Why Is High Cardinality a Problem?

Power BI's engine, VertiPaq, is masterful at compressing data. It achieves this by creating a dictionary for each column and then storing integer pointers instead of the raw data. When a column has a huge number of unique values, this compression becomes far less effective. The result?

• Larger .PBIX File Sizes: High-cardinality columns inflate the size of your data model, making your files bulky and harder to manage.
• Slower Data Refreshes: The engine has to work harder to process, encode, and compress columns with millions of unique values, leading to longer refresh times.
• Sluggish visuals and DAX measures: When you use a high-cardinality column in a visual, slicer, or measure, the engine has to scan a massive dictionary of unique values, slowing down calculations and rendering. It's the difference between looking up a word in a pamphlet versus a multi-volume encyclopedia.

How to Find High-Cardinality Columns

Before you can fix the problem, you need to find the offending columns. While there's no big red button that says "show high cardinality," here are a couple of reliable methods.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 1: Using the Power Query Editor

This is the most straightforward way to get a quick feel for your data.

1. In Power BI Desktop, go to the Home tab and click on Transform data to open the Power Query Editor.
2. Select a table you suspect might have issues.
3. Go to the View tab in the Power Query ribbon.
4. Check the boxes for Column quality, Column distribution, and Column profile.

Power Query will load previews of the data statistics at the top of each column. The "Column distribution" view will show you the number of distinct and unique values for each selected column. Keep an eye out for columns where the "distinct" count is very close to the total row count – that’s a sure sign of high cardinality.

Method 2: Using DAX Studio (for advanced users)

For a more comprehensive analysis of your entire data model, DAX Studio is an invaluable free tool. After connecting to your PBIX file, you can use the VertiPaq Analyzer feature. It provides a detailed table of every column in your model, including its cardinality and the total memory it consumes. This lets you pinpoint exactly which columns are causing the most bloat.

Practical Strategies to Reduce Cardinality

Once you've identified the problem columns, you can use several techniques to tame them. Most of these adjustments are made in the Power Query Editor and should be part of your regular data modeling best practices.

1. Remove Unnecessary High-Cardinality Columns

This sounds obvious, but it’s the most important and easiest fix. Do you really need to analyze by Transaction ID or a unique UUID for every event? Oftentimes, these columns are brought into the model but never used in any report visuals, relationships, or measures. They exist only to eat up memory.

How to fix it: In the Power Query Editor, simply right-click the header of the column you don't need (like TransactionID, CustomerID, or GUID) and select Remove. Every column you eliminate is a win for performance.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

2. Split DateTime Columns

A single DateTime column that includes timestamps down to the second (e.g., 2023-10-27 09:35:14) is a major source of high cardinality. Each second of the day creates a new unique value.

In most business reports, you rarely analyze data by the exact second someone clicked a link or bought a product. The date is important, and maybe the hour, but the rest is usually noise.

How to fix it:

1. In Power Query, select the DateTime column.
2. Go to the Add Column tab.
3. Click the Date dropdown and choose Date Only. This will create a new column with just the date part.
4. Select the DateTime column again. This time, go to the Time dropdown and choose Time Only. To take it a step further you can extract just the hour by using the 'Hour' option under Time.
5. Once you have the new Date and Hour columns, you can safely remove the original high-cardinality DateTime column. Your cardinality will drop dramatically. For example, a whole year of second-by-second data has 31.5 million unique values. A year of date-only data? Just 365.

3. Round or Bin Numeric Values

Columns with high-precision floating-point numbers, like sensor readings, stock prices, or calculated metrics, can also have very high cardinality. Do you need to analyze temperature to the fifth decimal place? Probably not.

You can reduce cardinality by rounding these numbers or grouping them into "bins" or ranges.

How to fix it:

1. Select the numeric column in Power Query.
2. Go to the Transform tab.
3. Click the Rounding dropdown and choose an appropriate level of precision (e.g., Round to 0 decimal places, round up, etc.).

For creating bins (e.g., for age groups like '0-10', '11-20'), you can use the Conditional Column feature on the Add Column tab. This allows you to define ranges and assign them a specific text label, turning a high-cardinality number column into a low-cardinality category column.

4. Group Similar Text or Categorical Values

Sometimes high cardinality comes from small, inconsistent variations in text-based data. For example, a "Country" column might contain "USA," "U.S.A.," "United States," and "US." To Power BI, these are four unique values, when they should all be one.

How to fix it:

1. In Power Query, select the column with inconsistent values.
2. On the Transform tab, you can use the Replace Values feature to standardize entries (e.g., replace "U.S.A." with "USA").
3. For more complex logic, the Conditional Column tool is perfect. You can create rules like: If [Country] equals "U.S.A." or [Country] equals "US", then the new value is "USA."
4. Alternatively, you can right-click the column and select Group By. This lets you aggregate rows but also clean up distinct values if used creatively with advanced options.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

5. Use Integers for Relationship Keys

In good data modeling, you separate your data into fact tables (like Sales) and dimension tables (like Products, Customers). The link between them is a key column.

If you're using a long, descriptive text column as the key (e.g., "Product Full Description Name Here"), you’re forcing Power BI to manage a high-cardinality text column. It's much more efficient to use a simple integer surrogate key (e.g., ProductID with values like 1, 2, 3...)

How to fix it: This is more of a data modeling strategy. If you control the data source (like a SQL database), ensure your dimension tables have a unique integer primary key. If you don't, you can create one in Power Query.

1. Create your dimension table in Power Query by right-clicking your main query and selecting Reference.
2. Remove all columns except the high-cardinality business key (e.g., "Customer Email") and any related attributes (like "Customer Name").
3. Right-click the business key column and choose Remove Duplicates. Now you have a clean list of unique customer emails.
4. On the Add Column tab, select Index Column. This will add a simple integer key (0, 1, 2, etc.) for each unique customer. This new Index column becomes your CustomerID.
5. Finally, merge this new dimension table back into your original fact table and expand only the new CustomerID. Then you can remove the high-cardinality "Customer Email" column from your fact table (it only needs to live in the dimension table).

This separates your unique values into a tidy dimension where it belongs, leaving your large fact table with a slim, low-cardinality integer column, which is much better for performance.

Final Thoughts

Reducing cardinality isn't about deleting data randomly, it's a strategic process of cleaning and optimizing your data model to keep only what you truly need for analysis. By removing unnecessary columns, splitting date/times, rounding numbers, and standardizing categories, you can dramatically improve report performance, speed up refreshes, and create a much better user experience.

Of course, spending your afternoons optimizing data models in Power Query isn't for everyone. If you’re a marketer or sales manager, this level of technical detail might feel like a distraction from your actual job. That's precisely why we built Graphed. We connect directly to your marketing and sales tools like Google Analytics, Shopify, HubSpot, and Salesforce, handling the data modeling and optimization automatically. Instead of wrestling with cardinality, you can just ask in plain English, "Show me my campaign ROI by channel," and get an interactive, live dashboard in seconds without touching Power Query.