MIN and MAX in Power BI - Deep Dive

Overview - MIN and MAX

What is it?

MIN and MAX are functions used in Power BI to find the smallest and largest values in a set of data. They help you quickly identify extremes, like the lowest sales or highest temperature. These functions work on columns or expressions and return a single value. They are simple but powerful tools for data analysis.

Why it matters

Without MIN and MAX, it would be hard to spot important data points like the best or worst performance quickly. These functions save time and help make decisions based on clear extremes. For example, a manager can see the highest sales day or the lowest stock level instantly. They make data insights easy and actionable.

Where it fits

Before learning MIN and MAX, you should understand basic Power BI concepts like tables and columns. After mastering these, you can learn related aggregation functions like AVERAGE or SUM. Later, you might explore more advanced calculations using DAX for dynamic analysis.

Mental Model

Core Idea

MIN and MAX pick out the smallest or largest number from a group, like choosing the shortest or tallest person in a room.

Think of it like...

Imagine you have a basket of apples of different sizes. MIN is like picking the smallest apple, and MAX is like picking the biggest apple. You don’t need to look at every apple again once you know these two.

┌───────────────┐
│   Data Set    │
│  [5, 3, 9, 1] │
└──────┬────────┘
       │
  ┌────▼─────┐   ┌────▼─────┐
  │   MIN    │   │   MAX    │
  │    1     │   │    9     │
  └──────────┘   └──────────┘

Build-Up - 6 Steps

1

Foundation

Understanding Basic Aggregation

Concept: Learn what aggregation means and why it is useful in data analysis.

Aggregation means combining many values into one summary value. For example, adding all sales to get total sales or finding the average price. MIN and MAX are special aggregations that find the smallest or largest value in a list.

Result

You understand that aggregation simplifies data by summarizing many numbers into one key figure.

Understanding aggregation is key because MIN and MAX are types of aggregation that help highlight extremes in data.

2

Foundation

Using MIN and MAX on Columns

Concept: How to apply MIN and MAX functions directly on a column in Power BI.

In Power BI, you can write a measure like MIN('Sales'[Amount]) to find the smallest sale amount. Similarly, MAX('Sales'[Amount]) finds the largest sale. These functions scan the entire column and return one number.

Result

You get the smallest and largest values from your data column instantly.

Knowing how to apply MIN and MAX on columns lets you quickly find key data points without manual searching.

3

Intermediate

MIN and MAX with Filters

🤔Before reading on: do you think MIN and MAX ignore filters or respect them? Commit to your answer.

Concept: MIN and MAX respond to filters and slicers in Power BI, changing their results dynamically.

If you apply a filter to show only sales from 2023, MIN('Sales'[Amount]) will find the smallest sale in 2023 only. This makes MIN and MAX very powerful for interactive reports where users explore data subsets.

Result

MIN and MAX values update automatically based on the filters applied in the report.

Understanding filter context is crucial because it makes MIN and MAX dynamic and responsive to user choices.

4

Intermediate

MIN and MAX with Expressions

🤔Before reading on: can MIN and MAX work on calculated values or only on raw columns? Commit to your answer.

Concept: MIN and MAX can be used on expressions, not just columns, allowing more complex calculations.

You can write MINX('Sales', 'Sales'[Amount] * 'Sales'[Quantity]) to find the smallest total sale value per row. MINX and MAXX iterate row by row, applying the expression before finding the min or max.

Result

You can find minimum or maximum values based on calculations, not just raw data.

Knowing MINX and MAXX lets you analyze more complex scenarios where values depend on multiple columns.

5

Advanced

Handling Blanks and Errors in MIN/MAX

🤔Before reading on: do you think MIN and MAX ignore blanks or treat them as zeros? Commit to your answer.

Concept: MIN and MAX ignore blank values but can be affected by errors or data types, which may cause unexpected results.

If your data has blanks, MIN and MAX skip them. But if there are errors or text in numeric columns, the functions might fail or return wrong results. You can use functions like IF or ISBLANK to clean data before applying MIN or MAX.

Result

You avoid errors and get accurate min/max values even with imperfect data.

Understanding how blanks and errors affect MIN and MAX prevents common bugs in reports.

6

Expert

Performance Considerations with MIN/MAX

🤔Before reading on: do you think MIN and MAX always run fast regardless of data size? Commit to your answer.

Concept: MIN and MAX are fast on columns but can slow down with complex expressions or large datasets, especially with MINX/MAXX.

MIN and MAX scan columns quickly using built-in engine optimizations. But MINX and MAXX evaluate expressions row by row, which can be slower. Using calculated columns or aggregations in the data model can improve performance. Also, understanding filter context helps avoid unnecessary calculations.

Result

You write efficient DAX that balances accuracy and speed.

Knowing performance trade-offs helps build responsive reports that scale well.

Under the Hood

MIN and MAX work by scanning the values in a column or expression and comparing each to find the smallest or largest. The engine uses optimized algorithms to do this quickly, often using indexes or columnar storage. When filters apply, the engine first narrows down the data subset, then finds min or max within that subset. MINX and MAXX iterate row by row, evaluating expressions before comparing values.

Why designed this way?

These functions were designed to be simple and fast for common tasks like finding extremes. The separation between MIN/MAX and MINX/MAXX allows users to choose between simple column scans and more flexible row-by-row calculations. This design balances performance and flexibility, avoiding complexity for basic use cases while supporting advanced scenarios.

┌───────────────┐
│   Data Table  │
│  (Filtered)   │
└──────┬────────┘
       │
┌──────▼───────┐
│ MIN / MAX    │
│  Engine      │
│  - Scans     │
│  - Compares  │
└──────┬───────┘
       │
┌──────▼───────┐
│ Result Value │
└──────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does MIN return zero if there are blanks in the data? Commit to yes or no.

Common Belief: MIN returns zero if there are blank or missing values in the data.

Reality: MIN ignores blank values and returns the smallest non-blank value.

Why it matters: Assuming blanks count as zero can lead to wrong conclusions, like thinking sales dropped to zero when data was just missing.

Quick: Can MIN and MAX be used on text columns to find alphabetically smallest or largest? Commit to yes or no.

Common Belief: MIN and MAX only work on numbers, not text.

Reality: MIN and MAX can work on text columns, returning alphabetically first or last values.

Why it matters: Knowing this lets you find earliest or latest names, categories, or dates stored as text, expanding your analysis options.

Quick: Do MIN and MAX always ignore filters applied in the report? Commit to yes or no.

Common Belief: MIN and MAX ignore filters and always calculate over the entire dataset.

Reality: MIN and MAX respect the current filter context and calculate over filtered data only.

Why it matters: Misunderstanding this causes confusion when results change unexpectedly with slicers or filters.

Quick: Does MINX run faster than MIN on large datasets? Commit to yes or no.

Common Belief: MINX is just as fast as MIN because they do the same thing.

Reality: MINX is slower because it evaluates an expression row by row, unlike MIN which scans a column directly.

Why it matters: Using MINX unnecessarily can slow down reports and frustrate users.

Expert Zone

1

MIN and MAX can be combined with CALCULATE to override filter context for advanced scenarios.

2

MINX and MAXX can handle complex expressions but require careful use to avoid performance hits.

3

The behavior of MIN and MAX with different data types (numbers, dates, text) varies subtly and affects results.

When NOT to use

Avoid using MINX or MAXX on very large datasets with complex expressions; instead, pre-calculate columns or use aggregations. For non-extreme summaries, use AVERAGE or MEDIAN. When needing multiple extremes, consider TOPN or RANKX functions.

Production Patterns

In real reports, MIN and MAX are often used in KPI cards to show best/worst values, in conditional formatting to highlight extremes, and combined with slicers for dynamic insights. Experts optimize performance by limiting MINX/MAXX usage and leveraging data model design.

Connections

Filter Context in DAX

MIN and MAX results depend on filter context, which controls what data is visible.

Understanding filter context helps you predict how MIN and MAX values change when users interact with reports.

Statistical Measures

MIN and MAX are basic statistical measures related to range and spread of data.

Knowing MIN and MAX helps grasp more complex statistics like quartiles and outliers.

Decision Making in Business

MIN and MAX identify extremes that often trigger business decisions, like lowest stock or highest sales.

Recognizing how these functions highlight critical points connects data analysis to real-world actions.

Common Pitfalls

#1 Using MINX on a column without an expression unnecessarily.

Wrong approach:

MINX('Sales', 'Sales'[Amount])

Correct approach:

MIN('Sales'[Amount])

Root cause:

Confusing MINX as always needed; it is only for row-by-row expressions, so using it on a simple column wastes resources.

#2 Expecting MIN to include blank values as zero.

Wrong approach:

MIN('Sales'[Amount]) returns 0 when blanks exist

Correct approach:

MIN('Sales'[Amount]) returns smallest non-blank value

Root cause:

Misunderstanding how blanks are treated in aggregation functions.

#3 Ignoring filter context causing unexpected MIN/MAX results.

Wrong approach:

Assuming MIN('Sales'[Amount]) always returns same value regardless of slicers

Correct approach:

Recognize MIN respects filters and changes with slicers

Root cause:

Not understanding dynamic filtering in Power BI reports.

Key Takeaways

MIN and MAX are simple but essential functions to find smallest and largest values in data.

They respect filters and slicers, making them dynamic and interactive in reports.

MINX and MAXX extend this power to calculated expressions but can impact performance.

Understanding how blanks and data types affect MIN and MAX prevents common errors.

Using these functions wisely helps create clear, actionable business insights quickly.

Practice

1.

What does the MIN function do in Power BI?

easy

A. It returns the smallest value in a column.

B. It returns the largest value in a column.

C. It counts the number of rows in a table.

D. It sums all values in a column.

Solution

1. Step 1: Understand the purpose of MIN

   The MIN function is designed to find the smallest value in a column of data.

2. Step 2: Compare with other functions

   Unlike MAX which finds the largest, MIN specifically returns the smallest value.

3. Final Answer:

   It returns the smallest value in a column. -> Option A

4. Quick Check:

   MIN = smallest value [OK]

Hint: MIN always finds the smallest number in a list [OK]

Common Mistakes:

• Confusing MIN with MAX
• Thinking MIN counts rows
• Assuming MIN sums values

2.

Which of the following is the correct syntax to find the maximum value in a column named Sales[Amount] using DAX?

easy

A. MAX(Sales[Amount])

B. MAXIMUM(Sales[Amount])

C. MAX_VALUE(Sales[Amount])

D. MAX(Sales.Amount)

Solution

1. Step 1: Recall correct DAX function name

   The correct function to find the largest value is MAX, not MAXIMUM or MAX_VALUE.

2. Step 2: Check column reference syntax

   In DAX, columns are referenced as TableName[ColumnName], so Sales[Amount] is correct.

3. Final Answer:

   MAX(Sales[Amount]) -> Option A

4. Quick Check:

   MAX + [Column] syntax [OK]

Hint: Use MAX(Table[Column]) to get largest value [OK]

Common Mistakes:

• Using wrong function names like MAXIMUM
• Using dot notation instead of brackets
• Adding extra words in function name

3.

Given the table Sales with Amount values: 100, 250, 75, 300, what is the result of MIN(Sales[Amount]) and MAX(Sales[Amount])?

medium

A. MIN = 75, MAX = 250

B. MIN = 100, MAX = 250

C. MIN = 0, MAX = 300

D. MIN = 75, MAX = 300

Solution

1. Step 1: Identify smallest value in Amount column

   The values are 100, 250, 75, 300. The smallest is 75.

2. Step 2: Identify largest value in Amount column

   The largest value among these is 300.

3. Final Answer:

   MIN = 75, MAX = 300 -> Option D

4. Quick Check:

   MIN = 75, MAX = 300 [OK]

Hint: MIN picks smallest, MAX picks largest number [OK]

Common Mistakes:

• Mixing up smallest and largest values
• Assuming zero is minimum without data
• Ignoring actual data values

4.

What is wrong with this DAX expression to find the minimum sales amount?
MIN(Sales.Amount)

medium

A. MIN cannot be used on numeric columns.

B. The table and column should be referenced with brackets, not dot notation.

C. The function should be MAX, not MIN.

D. The expression is correct as is.

Solution

1. Step 1: Check column reference syntax

   DAX requires columns to be referenced as TableName[ColumnName], not TableName.ColumnName.

2. Step 2: Validate function usage

   MIN is valid for numeric columns, so the function is correct but the syntax is wrong.

3. Final Answer:

   The table and column should be referenced with brackets, not dot notation. -> Option B

4. Quick Check:

   Use brackets for columns in DAX [OK]

Hint: Use brackets [ ] for columns, not dot . [OK]

Common Mistakes:

• Using dot notation instead of brackets
• Confusing MIN with MAX
• Thinking MIN can't be used on numbers

5.

You have a sales table with columns Region and SalesAmount. You want to create a measure that shows the maximum sales amount only for the East region. Which DAX expression is correct?

A) MAX(Sales[SalesAmount])
B) CALCULATE(MAX(Sales[SalesAmount]), FILTER(Sales[Region] = "East"))
C) MAXX(FILTER(Sales, Sales[Region] = "East"), Sales[SalesAmount])
D) MIN(Sales[SalesAmount])

hard

A. MAX(Sales[SalesAmount])

B. CALCULATE(MAX(Sales[SalesAmount]), FILTER(Sales[Region] = "East"))

C. MAXX(FILTER(Sales, Sales[Region] = "East"), Sales[SalesAmount])

D. MIN(Sales[SalesAmount])

Solution

1. Step 1: Understand filtering by region

   We want the max sales amount only for the East region, so we must filter the table to East.

2. Step 2: Evaluate options

   MAX(Sales[SalesAmount]) returns max for all regions.
   CALCULATE(MAX(Sales[SalesAmount]), FILTER(Sales[Region] = "East")) uses FILTER incorrectly; FILTER requires a table as the first argument followed by a condition.
   MAXX(FILTER(Sales, Sales[Region] = "East"), Sales[SalesAmount]) correctly filters Sales to East and then finds max SalesAmount.
   MIN returns minimum, not maximum.

3. Final Answer:

   MAXX(FILTER(Sales, Sales[Region] = "East"), Sales[SalesAmount]) -> Option C

4. Quick Check:

   Use MAXX + FILTER for conditional max [OK]

Hint: Use MAXX with FILTER for max with conditions [OK]

Common Mistakes:

• Using MAX without filtering
• Incorrect CALCULATE filter syntax
• Using MIN instead of MAX