Removing duplicates in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

DAX Measure to Count Unique Customers

You have a sales table with multiple rows per customer. Which DAX measure correctly counts the number of unique customers, removing duplicates?

A Unique Customers = COUNT(Sales[CustomerID])

B Unique Customers = COUNTROWS(Sales)

C Unique Customers = SUM(Sales[CustomerID])

D Unique Customers = DISTINCTCOUNT(Sales[CustomerID])

💡 Hint

Think about which function counts distinct values only.

DISTINCTCOUNT counts unique values, removing duplicates. Other functions count all rows or sum values.

❓ visualization

intermediate

Visualizing Unique Product Sales

You want to create a bar chart showing the number of unique products sold per region. Which approach correctly removes duplicates in the visualization?

A Use COUNT(Product[ProductID]) and place Region on axis

B Use COUNTROWS(Sales) and place Region on axis

C Use a measure with DISTINCTCOUNT(Product[ProductID]) and place Region on axis

D Use SUM(Sales[Quantity]) and place Region on axis

💡 Hint

Counting unique products means counting distinct product IDs.

Using DISTINCTCOUNT ensures duplicates are removed and unique products per region are counted.

❓ data_modeling

advanced

Removing Duplicates in Data Model

You have a table with duplicate customer records. Which Power BI data modeling step removes duplicates before loading data?

A Use Power Query's Remove Duplicates on CustomerID column

B Create a calculated column with DISTINCTCOUNT(CustomerID)

C Apply a filter visual to hide duplicates

D Use a DAX measure with COUNTROWS to filter duplicates

💡 Hint

Removing duplicates before loading data is done in Power Query.

Power Query's Remove Duplicates removes duplicate rows based on selected columns before data loads into the model.

🔧 Formula Fix

advanced

Debugging Duplicate Removal in DAX

You wrote this DAX measure to count unique orders but it returns incorrect results with duplicates still counted:

Unique Orders = COUNT(Order[OrderID])

What is the main issue?

Power BI

Unique Orders = COUNT(Order[OrderID])

A OrderID column has blanks causing errors

B COUNT counts all rows including duplicates; use DISTINCTCOUNT instead

C Measure syntax is invalid and causes error

D COUNT only works on numeric columns, OrderID is text

💡 Hint

Think about which function counts unique values.

COUNT counts all rows including duplicates. DISTINCTCOUNT counts unique values and removes duplicates.

🎯 Scenario

expert

Scenario: Removing Duplicates with Multiple Conditions

You have a sales table with duplicate rows for the same CustomerID and ProductID on the same date. You want to count unique sales transactions ignoring duplicates where CustomerID, ProductID, and Date are the same. Which DAX expression achieves this?

A Unique Sales = COUNTROWS(SUMMARIZE(Sales, Sales[CustomerID], Sales[ProductID], Sales[Date]))

B Unique Sales = DISTINCTCOUNT(Sales[TransactionID])

C Unique Sales = COUNTROWS(Sales)

D Unique Sales = CALCULATE(COUNT(Sales[CustomerID]), ALLEXCEPT(Sales, Sales[CustomerID], Sales[ProductID], Sales[Date]))

💡 Hint

Summarize groups rows by unique combinations of columns.

SUMMARIZE groups rows by CustomerID, ProductID, and Date, removing duplicates. COUNTROWS counts these unique groups.