9. Lighthouse

Level 8 · Keep the row's filter with KEEPFILTERS

From Olivia Grant, E-commerce Manager

“On the channel matrix, the Web Sales column shows the web number on the app row and the marketplace row too. People read it as app doing $3 million on the web, which makes no sense. Web Sales should only appear on the web row and the total, and be empty elsewhere.”

Your goal

The matrix has orders[channel] on its rows and the given measure Sales. Create a measure called Web Sales: Sales for the web channel that intersects with the row's channel instead of replacing it, so it is blank on the app and marketplace rows. Use CALCULATE with KEEPFILTERS.

  • Fill [Web Sales] on every row of the visual
  • The Total row right too

Your measure

DAX
Visual
A matrix: rows = orders[channel]; values = [Sales], [Web Sales]
Measures in the report
Sales = CALCULATE(SUM(order_items[line_total]), orders[status] = "completed")
Data pane: tables and columns
  • order_items order_item_id, order_id, product_id, quantity, unit_price, discount_pct, line_total
  • orders order_id, customer_id, order_date, status, channel, shipping_fee, coupon_code
  • products product_id, name, category, subcategory, unit_price, unit_cost, launched_on, is_active
  • customers customer_id, first_name, last_name, email, city, state, signup_date, segment, referral_source
  • returns return_id, order_item_id, returned_on, reason, refund_amount
  • Date Date, Year, Quarter, Month Number, Month, Year Month, Year Quarter, Day, Weekday Number, Weekday

Loading the editor…

    Visual[Web Sales] fills in when you press Check
    channelSalesWeb Sales
    app$2,024,728
    marketplace$837,483
    web$3,032,074
    Total$5,894,284

    Mote checks your result

    Press Check. Mote fills in your answer and compares it with what the report needs.

    The lesson

    CALCULATE has two ways to treat an existing filter on the same column: replace it (the default) or keep it and intersect (with KEEPFILTERS). Olivia’s matrix shows what goes wrong when you get the default without meaning to.

    What you are working in

    This page runs a practice DAX engine built for this site. It evaluates your measure on each channel row and the Total and compares every cell. In Power BI Desktop, select the measure in the Data pane, edit it in the formula bar, and press Enter; the matrix updates at once.

    Replace: the default

    Web Sales = CALCULATE([Sales], orders[channel] = "web")

    On the app row the filter context holds orders[channel] = "app". The filter argument is on the same column, so CALCULATE removes the app filter and puts web in its place. Every row shows about $3.0 million: the web number, regardless of the row. Sometimes that is exactly what you want (a “web benchmark” column next to each channel). Here it is not.

    Keep: intersect with KEEPFILTERS

    Web Sales = CALCULATE([Sales], KEEPFILTERS(orders[channel] = "web"))

    KEEPFILTERS tells CALCULATE to add the new filter on top of the existing one:

    Row Existing filter New filter Result
    app app web nothing in common: blank
    marketplace marketplace web blank
    web web web web sales
    Total none web web sales

    A blank in a matrix shows as an empty cell, which is what Olivia asked for.

    VALUES: what the filter context currently allows

    A related tool is VALUES(column), which returns the values visible in the current filter context: VALUES(orders[channel]) is { "app" } on the app row and all three channels on the Total. CALCULATE([Sales], FILTER(VALUES(orders[channel]), orders[channel] = "web")) gives the same result, because FILTER starts from the visible channels, but KEEPFILTERS says it more clearly. VALUES is also behind two helpers you will use often: HASONEVALUE(orders[channel]) is true when exactly one channel is visible (any row, but not the Total), and SELECTEDVALUE(orders[channel]) returns that one channel, or blank when there are several.

    When filters don’t collide

    KEEPFILTERS only matters when the filter argument and the existing filter touch the same column. CALCULATE([Sales], customers[segment] = "business") in a channel matrix never replaces the channel filter, because they are different columns; they combine anyway.

    Common mistakes

    • KEEPFILTERS outside CALCULATE. It only works as a filter argument of CALCULATE or CALCULATETABLE.
    • Expecting zero instead of blank. The intersection is empty, so the result is blank. Add + 0 only if the ticket asks for zeros; it also makes Power BI show rows it would hide.

    Cookie settings

    Pick what may run on this site. You can change this any time from "Cookie settings" at the bottom of every page.