EARLIER for row context in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate the rank of sales per product category using EARLIER

You have a table Sales with columns ProductCategory and SalesAmount. You want to create a calculated column CategoryRank that ranks each sale within its product category by sales amount, highest first.

Which DAX formula correctly uses EARLIER to achieve this?

A CategoryRank = COUNTROWS(FILTER(Sales, Sales[ProductCategory] = EARLIER(Sales[ProductCategory]) && Sales[SalesAmount] >= EARLIER(Sales[SalesAmount])))

B CategoryRank = RANKX(FILTER(Sales, Sales[ProductCategory] = EARLIER(Sales[ProductCategory])), Sales[SalesAmount], , DESC)

C CategoryRank = COUNTROWS(FILTER(Sales, Sales[ProductCategory] = Sales[ProductCategory] && Sales[SalesAmount] >= Sales[SalesAmount]))

D CategoryRank = COUNTROWS(FILTER(Sales, Sales[ProductCategory] = EARLIER(Sales[ProductCategory]) && Sales[SalesAmount] <= EARLIER(Sales[SalesAmount])))

💡 Hint

Remember, EARLIER refers to the current row's value in a nested row context.

Option A correctly uses EARLIER to compare each row's ProductCategory and SalesAmount to the current row, counting how many sales have equal or higher sales amount in the same category, producing a rank.

Option A uses RANKX but incorrectly uses EARLIER inside FILTER which is invalid here.

Option A compares columns to themselves without EARLIER, so it does not create a proper row context.

Option A ranks in ascending order, which is opposite of the requirement.

🧠 Conceptual

intermediate

Understanding EARLIER in nested row contexts

In DAX, what does the EARLIER function do when used inside nested row contexts?

A <p>It returns the value of the column from the outer row context.</p>

B <p>It returns the value of the column from the current row context only.</p>

C <p>It creates a new row context for the calculation.</p>

D <p>It aggregates values across all rows ignoring filters.</p>

💡 Hint

Think about how nested loops work in programming.

EARLIER allows you to access the value of a column from an outer row context when you have nested row contexts, like nested loops. This is useful to compare the current row with other rows.

🔧 Formula Fix

advanced

Identify the error in this EARLIER usage

Consider this calculated column formula:

Rank = COUNTROWS(FILTER(Sales, Sales[ProductCategory] = EARLIER(Sales[ProductCategory]) && Sales[SalesAmount] > Sales[SalesAmount]))

What error or issue will this formula cause?

A It will cause a syntax error due to missing ELSE in EARLIER.

B It will return the correct rank without errors.

C It will always return zero because the filter condition compares the same column without EARLIER on the right side.

D It will cause a type error because EARLIER cannot be used in FILTER.

💡 Hint

Check how the comparison is made inside the FILTER.

The formula compares Sales[SalesAmount] to itself without EARLIER on the right side, so the condition Sales[SalesAmount] > Sales[SalesAmount] is always false. This causes the filter to return no rows, so the count is always zero.

🎯 Scenario

advanced

Use EARLIER to calculate cumulative sales per customer

You have a Sales table with columns CustomerID, OrderDate, and SalesAmount. You want to add a calculated column CumulativeSales that shows the total sales amount for each customer up to and including the current order date.

Which DAX formula correctly uses EARLIER to calculate this cumulative sales?

A CumulativeSales = CALCULATE(SUM(Sales[SalesAmount]), Sales[CustomerID] = EARLIER(Sales[CustomerID]) && Sales[OrderDate] <= EARLIER(Sales[OrderDate]))

B CumulativeSales = SUM(Sales[SalesAmount]) + EARLIER(Sales[SalesAmount])

C CumulativeSales = SUMX(FILTER(Sales, Sales[CustomerID] = Sales[CustomerID] && Sales[OrderDate] <= Sales[OrderDate]), Sales[SalesAmount])

D CumulativeSales = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[CustomerID] = EARLIER(Sales[CustomerID]) && Sales[OrderDate] <= EARLIER(Sales[OrderDate])))

💡 Hint

Use FILTER inside CALCULATE with EARLIER to compare current row values.

Option D correctly filters the Sales table to rows with the same CustomerID and order dates up to the current row's date, then sums the sales amounts.

Option D lacks EARLIER and does not create proper row context.

Option D has incorrect syntax inside CALCULATE filter argument.

Option D incorrectly adds current sales amount to total sales without filtering.

❓ visualization

expert

Visualize sales rank per category using EARLIER

You created a calculated column CategoryRank using EARLIER to rank sales within each product category. You want to build a Power BI report page that clearly shows:

• Product categories on the Y-axis
• Sales amounts as bars
• CategoryRank as data labels on each bar

Which visualization and settings best follow best practices for clarity and accessibility?

A Use a pie chart showing SalesAmount by ProductCategory and add CategoryRank as legend. Use multiple bright colors without checking contrast.

B Use a clustered bar chart with ProductCategory on Y-axis, SalesAmount on X-axis, and enable data labels showing CategoryRank. Use high contrast colors and add alt text describing the chart.

C Use a stacked column chart with ProductCategory on X-axis, SalesAmount stacked by CategoryRank, and no data labels. Use default colors.

D Use a table visual listing ProductCategory, SalesAmount, and CategoryRank with no conditional formatting or accessibility features.

💡 Hint

Think about which chart type best shows ranking and sales amount clearly.

Option B uses a clustered bar chart which is good for comparing categories. Showing CategoryRank as data labels directly on bars helps users see rank clearly. High contrast colors and alt text improve accessibility.

Option B's pie chart is not ideal for ranking and can be confusing with many categories.

Option B's stacked column chart mixes rank and amount in a confusing way without labels.

Option B's table lacks visual impact and accessibility enhancements.