MIN and MAX in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate the minimum sales amount per product category

You have a sales table with columns ProductCategory and SalesAmount. You want to create a measure that returns the minimum sales amount for each product category.

Which DAX expression will correctly calculate this?

A MinSalesPerCategory = MAX(Sales[SalesAmount])

B MinSalesPerCategory = MIN(Sales[SalesAmount])

C MinSalesPerCategory = CALCULATE(MAX(Sales[SalesAmount]), ALL(Sales))

D MinSalesPerCategory = CALCULATE(MIN(Sales[SalesAmount]), ALLEXCEPT(Sales, Sales[ProductCategory]))

💡 Hint

Think about how to keep the filter on ProductCategory while calculating the minimum.

Option D uses CALCULATE with ALLEXCEPT to remove all filters except ProductCategory, so the minimum sales amount is calculated per category.

Option D calculates the minimum over the entire table ignoring categories. Options C and D use MAX which is incorrect.

❓ visualization

intermediate

Choose the best visualization for showing max and min sales per region

You want to show the maximum and minimum sales amounts for each region in a report. Which visualization type is best to clearly show both values side by side for easy comparison?

A Line chart showing max and min sales over time

B Clustered column chart with separate columns for max and min sales per region

C Pie chart showing max and min sales proportions per region

D Stacked column chart with max and min sales as stacked values

💡 Hint

Think about how to compare two values side by side for each category.

A clustered column chart shows max and min sales as separate columns next to each other per region, making comparison easy.

Stacked columns combine values, pie charts are not good for comparing two values per category, and line charts are better for trends over time.

❓ data_modeling

advanced

Create a calculated column to flag max sales per product

You have a sales table with multiple sales records per product. You want to add a calculated column that flags 1 if the sale amount is the maximum for that product, and 0 otherwise.

Which DAX expression correctly creates this calculated column?

A IsMaxSale = IF(Sales[SalesAmount] = CALCULATE(MAX(Sales[SalesAmount]), ALLEXCEPT(Sales, Sales[ProductID])), 1, 0)

B IsMaxSale = IF(Sales[SalesAmount] = MAX(Sales[SalesAmount]), 1, 0)

C IsMaxSale = IF(Sales[SalesAmount] = CALCULATE(MIN(Sales[SalesAmount]), ALLEXCEPT(Sales, Sales[ProductID])), 1, 0)

D IsMaxSale = IF(Sales[SalesAmount] > CALCULATE(MAX(Sales[SalesAmount]), ALL(Sales)), 1, 0)

💡 Hint

Remember to keep the filter on the current product when calculating the max.

Option A compares the current row's sales amount to the maximum sales amount for the same product using ALLEXCEPT to keep the product filter.

Option A compares to the max over the entire table, ignoring product. Option A uses MIN instead of MAX. Option A compares to max over all products, which is incorrect.

🔧 Formula Fix

advanced

Identify the error in this DAX measure for max sales per year

Given this DAX measure:

MaxSalesYear = MAXX(FILTER(Sales, Sales[Year] = MAX(Sales[Year])), Sales[SalesAmount])

What error or issue will this measure cause when used in a report?

Power BI

MaxSalesYear = MAXX(FILTER(Sales, Sales[Year] = MAX(Sales[Year])), Sales[SalesAmount])

A It causes a circular dependency error because MAX is used inside FILTER on the same column

B It returns the maximum sales amount for the current year context correctly

C It returns the maximum sales amount for all years ignoring filters

D It causes a syntax error due to incorrect use of MAXX

💡 Hint

Think about how MAX inside FILTER behaves when filtering the same column.

The measure uses MAX(Sales[Year]) inside FILTER on Sales[Year], causing a circular dependency because MAX tries to evaluate over a filtered column that depends on MAX itself.

This leads to an error in Power BI.

🧠 Conceptual

expert

Understanding MIN and MAX behavior with BLANK values

In Power BI, when calculating MIN or MAX over a column that contains BLANK() values, what is the behavior?

Choose the correct statement.

A MIN returns BLANK if any BLANK exists, MAX returns the maximum ignoring BLANK

B MIN and MAX treat BLANK as zero and include it in calculations

C MIN and MAX ignore BLANK values and return the minimum or maximum of non-blank values

D MIN and MAX return BLANK if any BLANK exists in the column

💡 Hint

Think about how aggregation functions treat missing or empty values.

MIN and MAX ignore BLANK values and calculate over the remaining non-blank values.

They do not treat BLANK as zero or return BLANK unless all values are BLANK.

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