Why MIN and MAX in Power BI? - Purpose & Use Cases

The Big Idea

What if you could instantly spot your best and worst numbers without scrolling endlessly?

The Scenario

Imagine you have a huge sales report in Excel and you need to find the smallest and largest sales amounts quickly.

You try scanning rows or sorting the data manually to spot these values.

The Problem

Manually scanning or sorting takes a lot of time and is easy to mess up, especially with thousands of rows.

You might miss updates or make mistakes when new data arrives.

The Solution

Using MIN and MAX functions in Power BI lets you instantly find the smallest and largest values in your data.

These functions update automatically when your data changes, saving time and avoiding errors.

Before vs After

✗ Before

Sort column and look for top and bottom values

✓ After

MIN(Sales[Amount])
MAX(Sales[Amount])

What It Enables

You can quickly highlight key insights like best and worst sales without digging through data manually.

Real Life Example

A store manager uses MAX to find the highest daily sales and MIN to spot the slowest day, helping plan promotions.

Key Takeaways

Manual searching for smallest and largest values is slow and error-prone.

MIN and MAX functions automate this, updating with your data.

This helps you focus on insights, not data hunting.

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