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.

Practice

1. What is the main difference between the DAX functions VALUES and DISTINCT?

easy

A. VALUES returns unique values including blanks, DISTINCT returns unique values excluding blanks.

B. VALUES returns unique values and blanks, DISTINCT returns unique values including blanks.

C. VALUES returns unique values excluding blanks, DISTINCT excludes blanks.

D. VALUES and DISTINCT are exactly the same in all cases.

Solution

1. Step 1: Understand VALUES behavior

   VALUES returns unique values from a column and includes blank values if they exist.

2. Step 2: Understand DISTINCT behavior

   DISTINCT returns unique values but excludes blank values from the result.

3. Final Answer:

   VALUES includes blanks, DISTINCT excludes blanks -> Option A

4. Quick Check:

   VALUES includes blanks, DISTINCT excludes blanks [OK]

Hint: Remember: VALUES keeps blanks, DISTINCT drops them [OK]

Common Mistakes:

• Thinking VALUES and DISTINCT always return the same results
• Assuming DISTINCT includes blanks
• Confusing the behavior of blank values in both functions

2. Which of the following is the correct syntax to get unique values from the column Product[Category] using DISTINCT?

easy

A. DISTINCT(Product[Category])

B. DISTINCT(Product.Category)

C. VALUES(Product[Category])

D. VALUES(Product.Category)

Solution

1. Step 1: Check correct column reference syntax

   In DAX, columns are referenced with table name and column name inside square brackets, like Product[Category].

2. Step 2: Check function syntax

   Both VALUES and DISTINCT take a column reference in parentheses. The correct syntax is DISTINCT(Product[Category]).

3. Final Answer:

   DISTINCT(Product[Category]) -> Option A

4. Quick Check:

   Correct syntax uses square brackets and function call [OK]

Hint: Use square brackets for columns inside functions [OK]

Common Mistakes:

• Using dot notation instead of square brackets
• Missing parentheses after function name
• Using incorrect column references

3. Given a table Sales with a column CustomerID containing values [1, 2, 2, 3, BLANK, 3], what will be the result of COUNTROWS(VALUES(Sales[CustomerID]))?

medium

A. 6

B. 3

C. 4

D. 5

Solution

1. Step 1: Identify unique values including blanks

   The unique values in CustomerID are 1, 2, 3, and BLANK. That is 4 unique values.

2. Step 2: Apply COUNTROWS on VALUES

   VALUES returns unique values including blanks, so COUNTROWS counts 4 rows.

3. Final Answer:

   4 -> Option C

4. Quick Check:

   Unique values including blank = 4 [OK]

Hint: VALUES includes blanks, so count them too [OK]

Common Mistakes:

• Ignoring blank values in count
• Counting duplicates multiple times
• Confusing DISTINCT behavior here

4. You wrote this DAX measure: UniqueProducts = DISTINCT(Product[ProductName]). When you use it in a card visual, it shows an error. What is the problem?

medium

A. The column name is incorrect; it should be ProductName[Product].

B. DISTINCT returns a table, but card visuals need a single value.

C. DISTINCT cannot be used in measures, only in calculated columns.

D. You need to wrap DISTINCT with SUM to get a number.

Solution

1. Step 1: Understand what DISTINCT returns

   DISTINCT returns a table of unique values, not a single number or scalar value.

2. Step 2: Understand card visual requirements

   Card visuals require a single scalar value, like a number or text, not a table.

3. Final Answer:

   DISTINCT returns a table, but card visuals need a single value. -> Option B

4. Quick Check:

   DISTINCT returns table, card needs scalar [OK]

Hint: DISTINCT returns tables; use aggregation for single values [OK]

Common Mistakes:

• Using DISTINCT directly in measures for visuals needing scalars
• Incorrect column references
• Thinking DISTINCT returns a number

5. You want to create a slicer that shows only unique non-blank customer names from the Customers[Name] column. Which DAX expression should you use to create a calculated table for this slicer?

hard

A. CustomerNames = FILTER(DISTINCT(Customers[Name]), NOT(ISBLANK(Customers[Name])))

B. CustomerNames = VALUES(Customers[Name])

C. CustomerNames = FILTER(VALUES(Customers[Name]), NOT(ISBLANK(Customers[Name])))

D. CustomerNames = DISTINCT(Customers[Name])

Solution

1. Step 1: Understand the need to exclude blanks

   Both VALUES and DISTINCT return unique values, but VALUES includes blanks while DISTINCT excludes them.

2. Step 2: Choose function and filter blanks

   DISTINCT returns unique values excluding blanks, which is perfect for a slicer.

3. Step 3: Compare options

   CustomerNames = DISTINCT(Customers[Name]) directly provides unique non-blank names without needing extra filtering.

4. Final Answer:

   CustomerNames = DISTINCT(Customers[Name]) -> Option D

5. Quick Check:

   DISTINCT excludes blanks for clean slicer [OK]

Hint: DISTINCT excludes blanks automatically [OK]

Common Mistakes:

• Using VALUES without filtering blanks
• Assuming DISTINCT includes blanks
• Using incorrect column references in FILTER conditions