VALUES and DISTINCT in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Difference between VALUES and DISTINCT in DAX

Given a table Sales with a column ProductCategory containing some blank values, what is the result of the following DAX expressions?

1. VALUES(Sales[ProductCategory])
2. DISTINCT(Sales[ProductCategory])

Which statement is true about their outputs?

A Both return the same unique values including blanks as a separate row.

B VALUES returns unique values including blanks; DISTINCT excludes blanks.

C VALUES returns unique values excluding blanks; DISTINCT includes blanks as a separate row.

D VALUES returns unique values excluding blanks; DISTINCT excludes blanks.

💡 Hint

Think about how blanks are treated by VALUES and DISTINCT in DAX.

Both VALUES and DISTINCT return unique values from a column, including blanks as a separate row if blanks exist.

❓ visualization

intermediate

Visualizing Unique Customers with VALUES vs DISTINCT

You want to create a card visualization showing the count of unique customers from the Orders table. Which DAX measure correctly counts unique customers including those with blank customer IDs?

A Unique Customers = COUNTROWS(VALUES(Orders[CustomerID]))

B Unique Customers = DISTINCTCOUNT(Orders[CustomerID])

C Unique Customers = COUNTROWS(DISTINCT(Orders[CustomerID]))

D Unique Customers = COUNTROWS(FILTER(Orders, NOT(ISBLANK(Orders[CustomerID]))))

💡 Hint

Consider which function directly counts unique non-blank values.

DISTINCTCOUNT counts unique non-blank values directly. VALUES and DISTINCT return tables, so COUNTROWS counts rows including blanks. Option A counts rows but excludes blanks.

❓ data_modeling

advanced

Using VALUES in a Relationship Filter Context

In a model with tables Sales and Products, you write this measure:

Filtered Sales = CALCULATE(SUM(Sales[Amount]), VALUES(Products[Category]))

What does the VALUES function do in this filter context?

A It causes a syntax error because VALUES cannot be used inside CALCULATE.

B It returns all unique product categories currently filtered in the report context.

C It returns a single product category regardless of filters.

D It returns all product categories ignoring any filters applied.

💡 Hint

Think about how VALUES behaves inside CALCULATE with filter context.

VALUES returns the unique values of Products[Category] in the current filter context, so CALCULATE applies a filter for those categories.

🔧 Formula Fix

advanced

Debugging Unexpected Results with DISTINCT

You wrote this measure to get unique product names:

Unique Products = COUNTROWS(DISTINCT(Sales[ProductName]))

But the result is higher than expected because some product names have trailing spaces. Which option best fixes this issue?

A Use TRIM inside DISTINCT: COUNTROWS(DISTINCT(TRIM(Sales[ProductName])))

B Replace DISTINCT with ALL: COUNTROWS(ALL(Sales[ProductName]))

C Use VALUES instead of DISTINCT: COUNTROWS(VALUES(Sales[ProductName]))

D Create a calculated column with trimmed names and use DISTINCT on it.

💡 Hint

Think about how to clean data before applying DISTINCT.

TRIM cannot be used directly inside DISTINCT because DISTINCT expects a column reference. Creating a calculated column with trimmed names cleans data before applying DISTINCT.

🧠 Conceptual

expert

Understanding VALUES Behavior with Multiple Columns

What happens when you use VALUES on a table with multiple columns, like VALUES(Sales) where Sales has columns OrderID and ProductID?

A VALUES returns a single column table with unique OrderID values.

B VALUES returns unique values only from the first column of Sales.

C VALUES returns a table with unique rows considering all columns in Sales.

D VALUES causes an error because it requires a single column reference.

💡 Hint

Consider how VALUES behaves when given a table instead of a column.

VALUES returns the unique rows of the entire table when used on a table reference, not just a single column.