What’s the difference between using ALL vs VALUES in MAXX table parameter?

When working with the MAXX function in Power BI and DAX, you may have come across two seemingly similar options: ALL and VALUES. Both are used to specify the table parameter in the MAXX function, but they serve distinct purposes and can produce different results. In this article, we’ll delve into the world of MAXX and explore the differences between using ALL and VALUES, helping you make informed decisions in your data modeling and analysis.

The MAXX function: A quick refresher

The MAXX function in DAX (Data Analysis Expressions) returns the maximum value in a column, ignoring any blank or null values. It’s commonly used to find the highest value in a dataset, such as the maximum sales amount or the highest temperature reading. The basic syntax of the MAXX function is:

MAXX(table, expression)

Where:

• table specifies the table that contains the column you want to analyze
• expression defines the calculation or operation to be performed on the column

Introducing the ALL and VALUES parameters

When using the MAXX function, you can specify an additional parameter to define the context in which the maximum value is calculated. This is where the ALL and VALUES parameters come into play.

ALL parameter

The ALL parameter is used to ignore any filters that may be applied to the table. When you use ALL, the MAXX function will consider all rows in the table, regardless of any filters that might be in place. This means that the maximum value will be calculated based on the entire dataset.

MAXX(ALL('Table Name'), 'Table Name'[Column Name])

In the example above, the MAXX function will return the maximum value in the ‘Column Name’ column, considering all rows in the ‘Table Name’ table, even if there are filters applied.

VALUES parameter

The VALUES parameter, on the other hand, is used to consider only the current filter context. When you use VALUES, the MAXX function will respect any filters that are applied to the table and only consider the filtered rows. This means that the maximum value will be calculated based on the current filter context.

MAXX(VALUES('Table Name'), 'Table Name'[Column Name])

In the example above, the MAXX function will return the maximum value in the ‘Column Name’ column, considering only the filtered rows in the ‘Table Name’ table.

Key differences between ALL and VALUES

Now that we’ve introduced the ALL and VALUES parameters, let’s summarize the key differences between them:

Parameter	Behavior	Filter Context
ALL	Ignores filters	Considers entire dataset
VALUES	Respects filters	Considers current filter context

When to use ALL vs VALUES

So, when should you use the ALL parameter, and when should you use VALUES? Here are some scenarios to help you decide:

Use ALL when:

• You want to calculate the maximum value across the entire dataset, ignoring any filters.
• You need to compare the maximum value across different filters or scenarios.
• You want to use the maximum value as a benchmark or target value.

Use VALUES when:

• You want to calculate the maximum value within the current filter context.
• You need to analyze the maximum value for a specific segment or group of data.
• You want to use the maximum value to drive further analysis or filtering.

Examples and scenarios

To illustrate the differences between ALL and VALUES, let’s explore some examples:

Example 1: Sales Analysis

Suppose you have a table with sales data, and you want to find the maximum sales amount for each region. You apply a filter to show only the sales data for the East region.

MAXX(ALL('Sales Data'), 'Sales Data'[Sales Amount])

In this case, using ALL would give you the maximum sales amount across the entire dataset, which might not be relevant for your analysis. Instead, you should use VALUES to respect the filter context:

MAXX(VALUES('Sales Data'), 'Sales Data'[Sales Amount])

This would give you the maximum sales amount for the East region specifically.

Example 2: Temperature Readings

Suppose you have a table with temperature readings for different cities, and you want to find the highest temperature reading overall.

MAXX(ALL('Temperature Readings'), 'Temperature Readings'[Temperature])

In this case, using ALL is appropriate because you want to consider the entire dataset to find the highest temperature reading.

Conclusion

In conclusion, the ALL and VALUES parameters in the MAXX function serve distinct purposes and can produce different results. Understanding the differences between these two parameters is crucial for making informed decisions in your data modeling and analysis. By using ALL, you can ignore filters and consider the entire dataset, while using VALUES respects the current filter context. By applying the correct parameter in the right scenario, you can unlock valuable insights and drive better business decisions.

Remember, when in doubt, ask yourself: Do I want to consider the entire dataset or the current filter context? The answer will guide you in choosing the correct parameter for your MAXX function.

Frequently Asked Question

When it comes to using MAXX table parameter, many users get stuck between choosing ALL and VALUES. Let’s dive into the differences!