What is a Scalar Value in Power BI?

Thinking about Power BI often brings to mind complex charts and endless data tables. Yet, some of the most powerful and useful calculations start with something far simpler: a single value. This individual value, known as a scalar value, is the basic building block for nearly every insightful metric and KPI you can create in a DAX formula. This article will show you what scalar values are, why they're so essential, and how you can harness them to build more powerful and flexible Power BI reports.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly Is a Scalar Value?

In the simplest terms, a scalar value is a single, solitary piece of data. It's not a row, not a column, and not a table. It's just one individual value.

Think about a single cell in an Excel spreadsheet. The content of that one cell - whether it's a number, a piece of text, or a date - is a perfect real-world example of a scalar value. In Power BI and DAX (the formula language used by Power BI), scalar values can come in a few basic forms:

• Numbers: This includes integers like 500 or decimal numbers like -99.45.
• Text Strings: Any piece of text enclosed in quotes, such as "Marketing Campaign" or "North America".
• Dates/Times: Values that represent a specific point in time, like January 21, 2024.
• Booleans: These represent a logical state, which can only be TRUE or FALSE.

Whenever you write a measure in DAX, your ultimate goal is almost always to produce a scalar value that Power BI can display in a visual, like a card, a bar chart, or a gauge.

Scalar vs. Tables: Understanding the Core Difference

Where many Power BI newcomers get stuck is failing to see the distinction between a scalar value, a column, and a table. This small point of confusion is often the source of frustrating errors when writing DAX code. Let's clarify the difference with a simple example.

Imagine you have a straightforward Sales data table like this:

In this context:

• A Table: The entire dataset above is a table. It has multiple rows and an arrangement of columns containing the order info.
• A Column: The 'Sales Amount' column is a list containing values for each sale in an order: 1200, 75, 50, and 1500. This is not a scalar value, instead, it's a collection of many values grouped into a data structure (in this scenario, a column with multiple rows).
• A Scalar Value: The number 1200 by itself is a scalar value. Similarly, the text string "USA" is a scalar, and so is the date 2024-05-01. Also - and most critically - the final number that results from an aggregation (which sums, averages, or counts all other values from one or even several tables) is another example, such as the total revenue from our small dataset here, returning 2825, which represents the result of the calculation 1200 + 75 + 50 + 1500.

DAX functions are designed to operate on these different data types in different ways. Some functions require a full table, some need a column as input to work correctly, and others will only accept a simple, scalar value.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Common Functions That Return Scalar Values

If nearly every calculation ends up as a scalar value, how do we get there? The answer lies in DAX aggregation functions. Their job is to accept a rich, multi-dimensional set of values, such as a full table holding information, perform a mathematical calculation on any of its columns, and boil it all down into a single, straightforward result.

The most common and valuable of these functions are known as aggregators, which collapse a column of numbers into one single resulting value. Some of the ones you'll use day in and day out will likely be:

Common Aggregation Functions:

• SUM(): This one totals up all the numeric values in a particular column. Its result is always a scalar value.
• AVERAGE(): Calculates the numeric average (in other words, the mean) of all the values contained in a column.
• COUNT(): As its English name implies, it will simply count the total number of rows contained in a table. It's useful to tally the number of times certain events take place, like the number of sales a business had last month.
• DISTINCTCOUNT(): On the other hand, this returns the number of all the unique values in a specified column.
• MIN(): Scans an entire data column for numeric and/or text input information. This will select the minimum, be it either number or alphabetical (first in the rank) depending on the case.
• MAX(): It behaves just like the previous and selects the numeric or string (alphabetical) last.

Example: Creating a 'Total Sales' Measure

To see how things work in practice, let’s go ahead and create a basic measure to sum the Sales Amount column.

1. Right-click on your Sales table in the Data pane and choose New Measure.
2. In the formula bar that'll pop up right beneath Power BI's menu, paste or type in the following DAX formula syntax:

Total Sales = SUM(Sales[Sales Amount])

In this expression, the SUM() function is responsible for taking every value in the Sales[Sales Amount] column as an argument (its input) and will later aggregate them so it can provide your newly created Total Sales measure a scalar value for their sum total (or final output).

From an interactive perspective, if you drag this Total Sales into any of your newly created visuals onto the reporting canvas - let's say a good old card visual - it will then display $2,825, since the total sum up is as per the DAX logic described in the previous sentence. What's interesting to observe now is that if you happen to also make use of 'slicers' (to filter out the data within the Power BI dashboard from USA and Canada, for instance), your DAX measure automatically updates its output and shows the relevant and updated scalar data. This interactivity lies at the very heart of DAX functionality, allowing you to build powerful and truly dynamic reports, based on scalar outputs responding to filter context modifications.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Other Functions for Selecting a Single Value

Aggregation is not the only way to fetch a scalar. Sometimes, you'll want to just pinpoint and fetch a very single, individual one without resorting to aggregation of multiple values inside a column or table.

A couple of useful functions to help in these situations are:

• SELECTEDVALUE(): This function is tremendously practical when dealing with a slicer, as it's able to track and return the value from a specified row whenever that's selected from a slicer visual. It also handles cases with many selected values by returning a previously set custom alternate message.
• LOOKUPVALUE(): This scans a specific column on a table, looks for a given value, and then fetches and returns the corresponding information from another value that could be located elsewhere in your data model.

Using SELECTEDVALUE to Display a Product's Name

Let's build a measure that only shows the name of a given product when only a single selection occurs:

Selected Products = "Currently, You've selected: " & SELECTEDVALUE(Products[Name], "Multiple items are selected")

If the user has only selected the Laptop product from a slicer, our measure will output Currently Selected: Laptop, but as soon as multiple selections take place, it will show Multiple items are selected as it's been predefined inside the DAX expression.

Understanding the Most Often Seen Power BI DAX Error

If you've been around the Power BI world for some time, you have most likely encountered this error message before. It's the most common and frustrating error a newcomer typically encounters. It essentially occurs when you're providing a column to a DAX expression that expects a single result.

When does this error occur?

• When you pass an unfiltered column into a function that only accepts one value as input: For instance, let's say you have the sales table and you're trying to build a text card with the product name by just passing Product Name in your measure. This is wrong. Power BI would complain as it cannot return Keyboard and Laptop at the same time.
• Using CALCULATE without a proper filter: The same may be true if you use it in more complex calculations where the output can yield multiple values.

How to solve it?

• Make use of aggregators: If you're working with numeric data, you can sum or average it out to return a single scalar value.
• Implement a filter: Let users pick which item they want and the result will update to show one single scalar value.
• Make sure to use SELECTEDVALUE on your team: This function is your friend and will keep these kinds of problems away.

Level Up Your DAX Skillset with VAR

As you get a bit more adventurous, you'll often encounter situations where more than one calculation is needed in order to generate a single output. Using the VAR declaration to store scalar values within your DAX code is a fantastic technique to keep your measures concise and readable.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Example - Using VARs to Calculate Margin

Profit Margin = VAR TotalRevenue = SUM(Sales[Revenue]) VAR TotalCost = SUM(Sales[Cost]) RETURN IF( TotalRevenue > 0, DIVIDE(TotalRevenue - TotalCost, TotalRevenue), 0 )

In the above calculation, TotalRevenue and TotalCost are variables, both holding scalar values produced by the SUM function. The calculation proceeds by using these variables to compute a margin, making the formula cleaner and more self-explanatory. This method allows you to break down a larger complex expression into a series of smaller pieces of logical code while improving the measure performance, as the variables inside will be calculated first, ensuring each variable is evaluated just one time.

All the way down, scalar values are Power BI's basic building block for its entire DAX language. By understanding what they are, as well as how to create and manage them with the help of DAX, a fantastic new world of opportunities to build and customize an infinite amount of custom calculations will open in your Power BI and DAX learning journey.

Final Thoughts

So, in summary: Scalar Values are a fundamental concept that will enable you to become a master in Power BI DAX. Their understanding will surely allow you to translate data and transform it into insight to make smarter and better business decisions in the future.

Our aim is to make business intelligence available for anyone. At Graphed, we believe it shouldn't have to take a lot of time and effort to understand this with one of the most popular BI tools in the industry. We built Graphed to allow users to connect their data sources and create all kinds of dashboards and reports by just using natural language, hence all the complexity-factoring DAX-like coding in the backend deliver the results to you instantly.

Feel free to try Graphed today to make collaborative marketing simple again.