VALUES and DISTINCT in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants to see a list of unique product categories sold last month and the total sales for each category.

📊 Data: You have a sales table with columns: Date, ProductCategory, SalesAmount.

🎯 Deliverable: Create a report showing unique product categories sold last month and their total sales.

Sample Data

Date	ProductCategory	SalesAmount
2024-05-01	Electronics	200
2024-05-03	Clothing	150
2024-05-05	Electronics	300
2024-05-10	Home	400
2024-05-15	Clothing	100
2024-05-20	Sports	250
2024-05-25	Home	350
2024-06-01	Electronics	500
2024-06-03	Sports	300

1

Step 1: Filter the sales data to include only rows where the Date is in May 2024.

FILTERED_SALES = FILTER(Sales, Sales[Date] >= DATE(2024,5,1) && Sales[Date] <= DATE(2024,5,31))

Expected Result

Filtered data contains only sales from May 2024.

2

Step 2: Create a new table of unique product categories sold in May 2024 using VALUES.

UniqueCategories = VALUES(FILTERED_SALES[ProductCategory])

Expected Result

UniqueCategories table contains: Electronics, Clothing, Home, Sports.

3

Step 3: Create a measure to calculate total sales for each product category in May 2024.

TotalSales = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[Date] >= DATE(2024,5,1) && Sales[Date] <= DATE(2024,5,31) && Sales[ProductCategory] = SELECTEDVALUE(UniqueCategories[ProductCategory])))

Expected Result

TotalSales measure sums sales amounts for May 2024 by product category.

4

Step 4: Build a table visual in Power BI with ProductCategory from UniqueCategories and TotalSales measure.

Create a one-to-many relationship from UniqueCategories[ProductCategory] to Sales[ProductCategory]. Add UniqueCategories[ProductCategory] to rows and TotalSales measure to values in the table visual.

Expected Result

Table shows each unique product category sold in May 2024 with its total sales.

Final Result

ProductCategory | TotalSales
----------------|----------
Electronics     | 500
Clothing       | 250
Home           | 750
Sports         | 250

✓Four unique product categories were sold in May 2024.

✓Home category had the highest total sales of 750.

✓Electronics and Sports had moderate sales.

✓Clothing had the lowest total sales among these categories.

Bonus Challenge

Create a measure that uses DISTINCT to count how many unique product categories had sales above 200 in May 2024.

Show Hint

Use DISTINCTCOUNT with a filter on SalesAmount > 200 and Date in May 2024.

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