Use Column Splitting to optimise Power BI semantic models

The short version

Column splitting is an idea worth testing when you are looking to reduce the size of a large semantic model to potentially reduce the SKU required to host the model. The good news is, the technique can also benefit the time to load data into a model and speed up your DAX queries.

Instead of storing each complete value in one column, split the column vertically and store it in smaller pieces. Then use a DAX measure to combine the pieces back together. Those pieces often repeat more frequently, which can help Power BI compress them.

What might improve?

• Reduce the amount of memory required to host the model
• Reduce the time to process an Import model, or page-in columns for Direct Lake models.
• Speed up your DAX queries and use less compute.

When does it fit? Large fact tables with numeric columns used mainly for SUM(). It also provides benefits for other simple aggregation functions like MIN/MAX/AVG etc, but not so much for Distinct Count.

What work is involved? You need to change data preparation and the affected measures, then check the results carefully. Remove the original column only when nothing else needs it. This is a modelling change, not a feature to switch on.

What is the catch? A table with many columns can still use more CPU when queried. Be sure to test loading and normal report queries separately and keep the split only if the trade-off suits your workload. Required numerical accuracy must not change.

Where should I start? Find one large candidate column in VertiPaq Analyzer and test it in a copy of the model.

The deeper dive

1. Choose a column worth the effort

Start with measured column size, not just the number of distinct values. An amount produced by a currency conversion can be a good candidate: many different amounts, lots of rows, and most reports only want a total. You can see the Total Size of a column using View Metrics (Vertipaq Analyzer) in DAX Studio. The Total size includes data along with any relevant dictionary numbers.

Check how the column is used across measures and reports. A distinct count of the original values cannot be reconstructed by adding distinct counts of the pieces. A filter such as Amount > 100, or grouping by individual amounts, also needs separate treatment if you have split the Amount column.

Do not confuse that with filtering the measure through a dimension. Date, Store and Product can still select the same fact rows, and both component sums use that selection.

Check simpler options first, including whether one appropriately scaled integer would suit the data. Do not remove precision the business needs just to improve a size chart.

2. Create the components upstream

For this example, assume the source amount is an exact decimal with four decimal places. Split it into a whole-number part and a signed fractional part stored as an integer.

These are illustrative values, not customer measurements:

The original value is the whole part plus the fractional part divided by 10,000. Notice that some pieces repeat even when the complete amounts differ. The row count does not change. It is this repetition of values in the new columns that allow better compression and therefore, smaller models, faster hydration and DAX queries.

One way to implement this in T-SQL is to scale the amount into an integer first. Here Amount is already DECIMAL(18,4), so the scaled value fits in a BIGINT. This is a projection of the relevant columns; keep the other fact columns your model needs.

WITH Scaled AS
(
    SELECT
        SalesKey,
        CAST(Amount * 10000 AS bigint) AS AmountScaled
    FROM dbo.FactSales
)
SELECT
    SalesKey,
    AmountScaled / 10000 AS AmountWhole,
    AmountScaled % 10000 AS AmountFraction
FROM Scaled;

With integer operands, T-SQL division truncates towards zero and the remainder keeps the original sign. NULL stays NULL in both components. Check those rules if you implement the split in another language.

For Import, create the components in the source or a folding Power Query transformation. For Direct Lake, write them into the Delta table during data preparation; a SQL view alone does not add the physical columns. Keep both components as Whole number columns in the model. Adding DAX calculated columns while retaining the original is not this storage replacement.

3. Change the measure, not every row

The original measure is straightforward:

Total Amount = SUM ( 'Sales'[Amount] )

The DAX expression for a split version is:

Total Amount =
    SUM ( 'Sales'[AmountWhole] )
        + DIVIDE ( SUM ( 'Sales'[AmountFraction] ), 10000 )

This combines two totals rather than reconstructing every fact row in a SUMX. In exact arithmetic the totals agree; real model data types still need the checks in the next step.

Power BI can combine compatible sums over the same table and filters in one storage engine request. This is Regular Fusion. The final division and addition operate on the totals, once per result cell, rather than once per fact row. Both component columns still need to be read and aggregated.

Keep the existing measure name and format where reports already use that measure, and hide the helper columns. Reports using the original column directly need their own compatibility check. Keep the original available in the test model while validating the replacement.

4. Check the answers before the timings

For a corporate model, a faster total is not useful if it is a slightly different total.

• Precision: the example starts with an exact four-decimal amount. Do not silently round a floating-point or six-decimal source to fit it.
• Signs and missing values: test positive, negative, zero, NULL and values either side of the split boundary. Check empty selections and all-BLANK groups in DAX too.
• Large totals: each row can fit its data type while the sum or an intermediate calculation overflows.
• Result type: DIVIDE returns a floating-point number, not Fixed decimal. At large magnitudes, this can affect required fractional digits. Check the required numerical accuracy, not just two equally formatted numbers on a screen.
• Report context: compare grouped results and grand totals under real filters and security roles. A matching grand total can hide errors that cancel each other out.

The extra precision notes at the end explain how to choose a common scale. Resolve these checks before treating a smaller representation as a successful optimisation.

5. Compare the whole cost

Change one thing at a time. Use the same source rows, model relationships, filters and security context for both representations. Run repeated queries with the same cache policy, and distinguish actual execution from a cached result.

Record three things:

• Size: compare the original column with all replacement columns combined. Separate dictionary size from encoded data size, and include hierarchy/index sizes where available. Columns coexisting in a test model do not demonstrate the whole-model saving after replacement.
• Loading or processing: for Import, measure refresh/processing and include the upstream transformation cost. For Direct Lake, measure loading the required columns into memory. A model refresh, also called framing, is not the same operation as loading those columns.
• Queries: compare elapsed time and CPU with the required columns already in memory. In Server Timings, inspect the generated storage engine query, often called xmSQL, to check whether the component sums are in the same request.

Keep the measurement labels honest. Delta-file size is not resident model size. Adding the durations of overlapping loads does not give elapsed page-in time. Lower query CPU does not automatically mean the same percentage reduction in elapsed time or your capacity bill.

Why a smaller model can still cost more CPU

Where dictionary encoding is used, the dictionary holds the distinct values. The data chunks hold the encoded values for the rows.

A split can shrink the dictionaries enough to reduce the overall footprint, while making the combined data chunks larger. Once the columns are loaded, the query still has to process those component streams. Fusion does not remove that work.

That is one reason to look beyond the total size figure. More encoded data can contribute to higher query CPU, but bytes alone do not predict CPU. Encoding and the actual work performed also matter, and SUM can use dictionary values too.

Consider your workload. Columns that load once and serve many queries give a warm-query CPU penalty many opportunities to accumulate. If memory pressure repeatedly forces data to be loaded, a smaller footprint and cheaper loads may be more valuable. Measure which situation your users actually experience.

6. Roll out only what passed the test

Whole and fractional parts are one possible split, not a universal best choice. If it does not help, try another boundary based on the data’s range and distribution, or keep the original. More components or evenly sized dictionaries are not goals in themselves.

Before deployment, check the source transformation and all affected measures into your normal release process. Remove the original from the replacement model only after checking dependencies and results. Hiding it does not remove its storage cost. Retain a way to restore the original definition and measure if the wider workload exposes a problem.

Recheck the final model’s size and representative reports after removal. That is the implementation worth sharing, not just a promising test with two extra columns.

Extra detail: choosing a scale without losing precision

The example assumes an exact four-decimal source. Multiplying an arbitrary floating-point value by 10,000 and converting it to an integer is not automatically lossless.

Start with the source type and the digit positions needed across the column. For DECIMAL(p,s), p is the declared precision and s is the scale, not a measurement of how many positions the values actually use. The largest integer part and the deepest required fractional position can occur on different rows.

For example, these are three illustrative values:

A common fixed-decimal representation for those values needs 11 integer positions and six fractional positions, or 17 in total. That is different from counting significant digits in each value.

String length is not a reliable shortcut. A conversion of 12345678901.23 to 1.234567890123E10 has 17 characters, but the value needs 13 decimal digit positions. A minus sign, a decimal point, an exponent and formatting zeros can all affect the length. With a floating-point source, the rendered digits also cannot tell us the original intended decimal scale. Establish that from the source definition or generation logic before scaling.

Leading zeros in a component are safe when its position is fixed. With a six-place fractional component, 000012 can be stored as the integer 12 and reconstructed as 12 / 1000000. Do not choose the divisor from the component’s displayed length. Moving a split boundary redistributes digits; it must not silently round them away.

Keep the required source scale throughout. Power BI’s Fixed decimal number type has four decimal places, so choosing it is not a substitute for checking a source that needs more. Exact source components also do not remove the need to validate the DAX result type and totals.

Further reading

• Data reduction techniques for Import modelling
• How Direct Lake works
• Data types in Power BI