Level 8 · Boss: rescue the slow discount report

Patrick’s report is slow for the usual three reasons at once: a measure that works row by row, a measure that repeats itself, and an import that carries far more than anyone reads. This case fixes all three, in the order you would at work.

What you are working in

A practice report and a practice Power Query editor, not Power BI. Steps 1 and 2 are DAX measures checked cell by cell in a matrix with 'Date'[Year] on its rows; step 1 also checks that FILTER is gone. Step 3 is a list of Applied Steps on the imported order lines, checked against the expected table.

In Power BI Desktop: before you start, Optimize → Performance analyzer → Start recording → Refresh visuals and note the DAX query times. Edit the measures in the formula bar, open the query with Home → Transform data, and record again at the end.

Step 1: from FILTER to column filters

The old measure walks every order line, looks up the order’s status with RELATED, keeps the discounted completed lines, then sums them. Both conditions are plain tests on one column each, which is exactly what CALCULATE filter arguments are for:

Discounted Sales = CALCULATE(SUM(order_items[line_total]),
    order_items[discount_pct] > 0, orders[status] = "completed")

The status filter on orders reaches order_items through the relationship, and the year on the matrix rows reaches it through 'Date' → orders. Same numbers, one storage engine scan.

Step 2: a share without repeats

Discounted Share =
VAR discounted = [Discounted Sales]
VAR total = [Sales]
RETURN IF(total > 0, DIVIDE(discounted, total))

Each measure is evaluated once per cell and reused. DIVIDE protects the Total row and any year with no sales. Format it as a percentage in Power BI with Measure tools → Format → Percentage.

Step 3: import only discounted lines

The source brings eight columns for every line of the day. The report needs four, and only for discounted lines:

Filter rows: discount_pct > 0
Choose columns: order_id, product_id, quantity, discount_pct

Both steps fold into the SQL query that Power Query sends, so SQL Server returns the small result and Power BI never stores the rest. Filter first: once you choose the four columns, the filter still works here, but in real reports the column you filter on is often one you drop.

What to check afterwards

Record Performance analyzer again. The discount visuals’ DAX query time should drop, the refresh should be shorter, and the model smaller (DAX Studio’s View Metrics shows each table’s size). If one visual is still slow, its measure is next in line.