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.