What Should You Put Where the Table Belongs in the New Visual Calculations? – A Complete Understanding of the ROWS() Function

When you first start using the new Visual Calculations feature in Power BI, you will surprisingly run into a bottleneck at the very beginning. This occurs precisely when attempting to deploy iterator functions such as SUMX, AVERAGEX, MAXX, and FILTER.

Code that we used to take completely for granted in standard DAX suddenly stops working.

SUMX(
  Sales,
  Sales[Qty] * Sales[Price]
)

In a traditional measure, you simply needed to place the Sales table into the first argument. In a Visual Calculation, however, the exact moment you input a table name, an error is triggered.

Initially, I assumed I was simply mistaken about the syntax. However, after days of testing various scenarios, I realized something fundamental: Visual Calculations operate on an entirely different paradigm from traditional DAX.

Today, based on the trial and error I personally experienced, I would like to answer the question: "What exactly should be placed in the first argument of SUMX?"

1. Example Data

Let us assume a simple dataset as shown below.

f_Sales Table

Year Month Category Sales
20241Coffee100
20241Tea200
20241Juice150
20251Coffee130
20251Tea260
20251Juice180

Next, we create a single measure:

Total Sales = SUM(‘f_Sales’ [Sales])


Total Sales = SUM(‘f_Sales’ [Sales])


Following this, a Matrix visualization is generated as shown below.


Matrix visualization in Power BI


2. The Traditional DAX Paradigm

In a traditional measure, SUMX always iterated through a specific table.

DAX SUMX



That is, the architecture followed this explicit sequence:

Sales Table
↓
Iterate row by row
↓
Perform evaluation

Consequently, the very first question when writing a SUMX expression was always: "Which table am I going to iterate through?"

3. The Shift in Paradigm for Visual Calculations

Visual Calculations do not directly traverse the underlying data model. Instead, they treat the exact results currently visible on the screen as a single virtual table.

If you attempt to create a new Visual Calculation named Total By SUMX on the table above and place an equivalent measure into the calculation, an error occurs stating that the 'f_Sales' table cannot be found.

[Link] – Creating a New Visual Calculation

error occurs stating that the 'f_Sales' table cannot be found.



Therefore, instead of referencing a model table like 'f_Sales', you must now reference the specific set of rows rendered on the screen.

4. Enter ROWS()

The function that represents the set of rows currently displayed on the screen within a Visual Calculation is ROWS().

currently displayed on the screen within a Visual Calculation is ROWS()



For example, if you write the expression:

Total By SUMX = SUMX( ROWS(), [Total Sales])

The ROWS() function will iterate through the Coffee, Tea, and Juice rows currently visible on the screen one by one.

The evaluation process unfolds as follows:

  • Coffee → 130
  • Tea → 260
  • Juice → 180
  • Total: 570

5. The One Formula to Remember

1) Traditional DAX

  • SUMX( TableName, Expression )

2) New Visual Calculation

  • SUMX( ROWS(), Expression )

In short, whereas traditional DAX iterated over a "table within the data model," a Visual Calculation iterates over the "rows currently visible on the screen."

6. Wrapping up

When using Visual Calculations for the first time, the most frequently asked question is this: "I want to use SUMX or FILTER, but what should I put in place of the table?"

The answer is remarkably straightforward: simply use ROWS(), which represents the rows currently displayed on the screen.

While traditional DAX iterated through the tables of a data model, the new Visual Calculations feature iterates through the row set of the visualization currently being viewed by the user.

Therefore, when utilizing iterator functions (X-functions) within a Visual Calculation, you only need to remember this single sentence:

"Put ROWS() where a table is required. This is because the table for a Visual Calculation is the current screen itself."

The moment you grasp this concept, most iterator functions—such as SUMX, AVERAGEX, MAXX, and FILTER—will begin to appear far more intuitive.

<Other posts on the blog>

Comments

Post a Comment

Popular posts from this blog

DAX CALENDAR Function Deep Dive and Practical Usage Guide

Image
The CALENDAR function is essential for creating a date table in a data model, and it plays a vital role when performing time intelligence calculations. Through this document, you'll be able to get a detailed understanding of the CALENDAR function's syntax, how it operates, and how you can leverage it in real-world data analysis scenarios.   1. What Exactly is the CALENDAR Function? The CALENDAR function in DAX is basically your go-to tool for building a dedicated date table based on a specific date range. All you do is give it a start date and an end date, and it spits out a single-column table containing every single date within that range. This generated table is what we commonly call a 'Date Dimension Table. ' It's absolutely critical for your data model because you'll use it to set up relationships with other tables (like sales, orders, or inventory) and, more importantly, it becomes the foundation for all your time-based analysis.     1.1. Syntax ...
Read more

Standard Deviation (Part 2): Strategic Limitations and Complementary Perspectives in Standard Deviation Analysis

Image
As we've seen, standard deviation is more than just measuring 'how spread out the data is' it quantifies that spread to evaluate the stability and risk of business performance, thus serving as a key managerial analysis metric that supports strategic decision-making. However, relying solely on standard deviation for interpretation can easily lead to misjudgment. When using standard deviation for corporate, sales/profit analysis, or forecasting, the following additional factors must be considered: If you missed 'Standard Deviation' Previous contents, Please check out the below link. Standard Deviation The Complete Guide to the Core of Business Data Analysis Standard Deviation (Part 1): Measuring Data Volatility and Using the Insights for Better Strategy   1. Considering the Relationship with the Mean (Expected Value) Standard deviation represents only the absolute degree of data 'spread.' Therefore, to grasp the relative meaning of this figure, it must be cons...
Read more

Standard Deviation (Part 1): Measuring Data Volatility and Using the Insights for Better Strategy

Image
Standard Deviation is a core metric that quantifies the 'dispersion' of your data, showing—as a single number—how tightly clustered or widely spread the data points are from the mean. A larger number means the data is widely spread out, while a smaller number means it's tightly grouped. Let's explore how this concept is used strategically in analyzing sales and profits. If you missed 'Standard Deviation' first contents, Please check out the below link. Standard Deviation The Complete Guide to the Core of Business Data Analysis A nd you can also refer next contents, after reading this. Standard Deviation (Part 2): Strategic Limitations and Complementary Perspectives in Standard Deviation Analysis   1. Why We Must Read the 'Wobble' (The Trap of the Mean) It's natural to first look at average sales or profit when analyzing a company's performance. The average is the central value of the data, making it the most fundamental and useful metric for gr...
Read more