# DAX HANDBOOK 4.4 Table to Scalar Transformation

If you wish to follow along, you can find PBIX/Excel files at the bottom of the article.

### Guide Topics (red is currently selected one)

#### Krešimir Ledinski

Microsoft certified expert in the field of Business Intelligence. His biggest passions are DAX, M, and data modeling.

Experienced data analyst. Advanced in SQL, PowerApps and M language.

## Explanation

All table functions have one special feature. If the result of the table function returns exactly one row and a single column, its result is automatically transformed into a scalar which can be shown in a visual.

```NumberOfCategories = COUNTROWS(VALUES(Sales[CategoryName]))
NameOfCategory = IF(COUNTROWS(VALUES(Sales[CategoryName]))=1,VALUES(Sales[CategoryName]),"Multi category")
```

The [NumberOfCategories] is a measure that counts the number of unique CategoryNames from the Sales table, taking into account the Color filter on the visual. We can see that Multi and White colors have only one type of CategoryName. VALUES function used a single column as an argument, and that column, when filtered by colors Multi and White, returns only a single row. When a table function returns only one row and one column, its result is automatically transformed into a scalar which can be represented in a visual. That’s why we receive “Clothing” text as a result of a measure for the mentioned colors.

## Important

When using this technique, it’s extremely important to always secure the Measure to perform scalar transformation only in case the 1 row 1 column rule is secured. In our case, we implemented an IF condition, which counts the number of rows in a table that the VALUES function returns. If count does not equal to 1, then the text returned in a visual is “Multi category”. If the value equals 1, it means it’s safe to make table to scalar transformation and we simply return the result of the VALUES function.

Table to scalar transformation occurs for every table function which, after evaluation, returns a single row and a single column.

[NameOfCategory] measure can be also written with a sugar syntax function SELECTEDVALUE(ColumnName, “Alternate Result”).

## Materials

Show All DAX Guide Topics (click to expand)

### We wish to create the best possible content!

If you are a novice looking for a better explanation of any element of the topic, feel free to comment on the part you didn't quite understand!

If you are an expert in DAX and believe certain topic lacks important internals, your comments are more than welcomed!

Subscribe
Notify of
Inline Feedbacks

### OUR SERVICES

Prefer live training or consultations?

### DAX HANDBOOK7. VARIABLES

Variables are used in almost every measure you will create. The reason we introduce them last is that they use all other parts of DAX code to produce faster, more powerful and maintainable code. Variables are like containers of a part of the DAX code which can be used throughout...

### DAX HANDBOOK6.8 ALLSELECTED

Explanation ALLSELECTED is one of the most complex functions in DAX. When used improperly it can lead to unexpected results. As a rule of thumb, you should not use it in iterative functions. It is preferable to use it only as a CALCULATE filter remover, not as a table function....

### DAX HANDBOOK6.7 Lineage

What is Lineage? Lineage is a part of DAX mechanics that enables us to use tables as filter arguments for the CALCULATE function. It’s also used in row2filter context transition and other aspects of the data model that involve filter propagation throughout the model. We can state that lineage is...