Power BI: Use .values() with JavaScript

Key insights

• .values() (JavaScript) vs VALUES() (DAX)
  JavaScript’s .values() is not a DAX method. DAX has its own VALUES() function used inside measures and queries. Use JavaScript only when building custom visuals or external scripts, not for native DAX iteration.
  
• Iterators (X functions)
  Functions like SUMX and AVERAGEX run row-by-row over a table or virtual table. Use these to apply custom calculations that simple aggregations cannot do.
  
• What VALUES() does
  VALUES() returns a one-column table of distinct values from a column in the current filter context. It creates a virtual table you can iterate over to avoid duplicated rows and to respect active filters.
  
• VALUES() vs DISTINCT()
  Both return distinct lists. Use VALUES() when you need DAX-specific filter propagation or relationship behavior. Use DISTINCT() for straightforward de-duplication when you don’t need special filter semantics.
  
• Practical tips and performance
  Store virtual tables in variables, iterate over keys (not the full fact table), and combine VALUES() with FILTER, ADDCOLUMNS, and CALCULATE to shape context. Test measures for performance on large models.
  
• When to use VALUES while iterating
  Use VALUES() when you need a distinct list of keys or categories to iterate over, when you must preserve current filters, or when you want to reduce duplicate computations inside SUMX or similar iterators.
  

SQLBI's recent YouTube video breaks down a common point of confusion for Power BI developers: when and why to use VALUES() while iterating a table reference in DAX. The presenter clarifies that DAX does not implement a JavaScript-style .values() method, yet the idea of extracting distinct values during iteration is central to many DAX patterns. Consequently, the video focuses on how DAX’s own functions and virtual tables achieve similar goals, and why developers should choose one approach over another. Overall, the content aims to help readers avoid conceptual mix-ups and to improve both correctness and performance.

Video overview and core message

The video from SQLBI opens by separating the JavaScript mental model from the DAX mental model, which helps set realistic expectations. It then demonstrates practical examples where iterating over distinct values matters for calculations such as running totals, group-level measures, or conditional aggregation. Importantly, the presenter highlights that iteration in DAX relies on functions with row-wise evaluation semantics, rather than on object methods familiar to JavaScript developers. Thus, the core message is that understanding DAX’s functional tools leads to clearer, faster measures.

Understanding VALUES() and common confusion

In the video, SQLBI explains that VALUES() in DAX returns a table of distinct values from a column or a one-column table when used in an expression, and that its behavior depends on the current filter context. The author contrasts this with a JavaScript .values() mindset, which can lead developers to expect in-place iteration or object-like behavior that DAX does not provide. As a result, the video encourages viewers to think in terms of virtual tables and filter contexts rather than procedural loops. This shift in thinking reduces bugs that stem from misapplied patterns.

Iteration patterns: SUMX, FILTER and virtual tables

SQLBI demonstrates practical iteration using functions like SUMX and AVERAGEX, showing how they evaluate an expression for each row of a supplied table or virtual table. Furthermore, the video covers how FILTER and CALCULATE help to sculpt the rows available for iteration by changing the filter context during evaluation. Consequently, combining these functions allows complex, row-aware logic that would otherwise require cumbersome workarounds. The presenter also stresses the value of creating small virtual tables that limit cardinality before iterating.

Tradeoffs between clarity and performance

As the video notes, developers must balance readability and maintainability against raw performance when choosing iteration techniques. For example, using VALUES() or DISTINCT can make logic clearer by explicitly showing the set of items to iterate, yet those choices may increase intermediate memory usage on large models. Conversely, highly optimized expressions that avoid creating intermediate tables can run faster but become harder to understand and maintain. Therefore, SQLBI recommends starting with readable DAX, then profiling and optimizing only where performance issues appear.

Challenges in real-world models

The presenter addresses several practical challenges, including context transition, handling BLANK values, and managing high cardinality columns. For instance, VALUES() can return unexpected BLANK rows when filters remove all values, which changes measure behavior unless handled deliberately. Additionally, iterating over columns with millions of distinct values risks heavy memory use or slower query plans, so the video suggests summarizing or grouping first. Finally, SQLBI emphasizes testing edge cases and validating results after refactoring measures.

Practical recommendations and next steps

To conclude, SQLBI offers straightforward recommendations: use VALUES() when you need a distinct set that respects the current filter context, prefer virtual tables to control cardinality, and choose iterating X functions for row-wise logic. Then, test performance using available profiling tools and refactor only when necessary so that clarity is not sacrificed needlessly. The video also encourages learners to study virtual table patterns and to practice on representative data sets to understand tradeoffs. Ultimately, the goal is to write correct, maintainable measures that scale for real-world reports.

From an editorial perspective, the video provides a useful bridge between conceptual misunderstandings and practical DAX usage. By combining examples, clear explanations, and performance-minded tips, SQLBI helps viewers reconcile different programming mental models and apply the right DAX constructs. Consequently, this resource is valuable for both intermediate authors who want fewer surprises and advanced authors who need to validate performance choices. In short, the video sharpens judgment for when to use VALUES() and when to prefer alternative patterns.

Keywords

Power BI DAX .values() JavaScript iterate, DAX .values function tutorial, Iterate DAX values with JavaScript, Power BI JavaScript API DAX examples, DAX iteration techniques, Power BI custom visual iterate values, Optimize DAX .values performance, Use .values() in Power BI measures