MIN and MAX in Power BI - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the MIN function do in Power BI?

The MIN function returns the smallest number from a column or expression. It helps find the lowest value in your data.

beginner

What is the purpose of the MAX function in Power BI?

The MAX function returns the largest number from a column or expression. It helps find the highest value in your data.

beginner

Write a simple DAX formula to find the minimum sales amount from a Sales table.

MIN_Sales = MIN(Sales[Amount])

beginner

Write a simple DAX formula to find the maximum sales amount from a Sales table.

MAX_Sales = MAX(Sales[Amount])

intermediate

Can MIN and MAX functions be used with dates in Power BI?

Yes, MIN and MAX can find the earliest and latest dates in a date column, helping you understand time ranges in your data.

What does the MAX function return in Power BI?

A The smallest value in a column

B The largest value in a column

C The average value in a column

D The total sum of a column

MAX returns the largest value found in the specified column or expression.

Which DAX formula finds the minimum value in the 'Price' column of the 'Products' table?

A SUM(Products[Price])

B MAX(Products[Price])

C AVERAGE(Products[Price])

D MIN(Products[Price])

MIN(Products[Price]) returns the smallest price value in the Products table.

Can MIN and MAX functions be used on text columns in Power BI?

A Yes, they find the shortest and longest text

B No, they only work with numbers

C No, they only work with numbers and dates

D Yes, they find alphabetical first and last

MIN and MAX work only with numeric and date/time data types, not text.

If you want to find the earliest date in a 'Date' column, which function do you use?

A MIN(Date[Date])

B MAX(Date[Date])

C SUM(Date[Date])

D AVERAGE(Date[Date])

MIN returns the earliest (smallest) date value in the column.

What will this DAX expression return? MAX(Sales[Quantity])

A The largest quantity sold

B The smallest quantity sold

C The total quantity sold

D The average quantity sold

MAX(Sales[Quantity]) returns the highest quantity value sold.

Explain how MIN and MAX functions help analyze data in Power BI.

Think about how you find the lowest or highest number in a list.

Write a DAX formula to find the maximum date in a 'Orders' table and explain what it shows.

Use MAX on a date column to find the newest date.

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