8. Lighthouse

Level 1 · What a slicer does to a measure

From Rachel Moreno, Regional Sales Manager

“On the channel page I picked web in the slicer, and now every number on it is web only. That's what I wanted for the Sales column, but I also need each category's sales across all channels right next to it, so I can see how much of it web brings. Can the second column ignore the slicer?”

Your goal

The matrix has products[category] on its rows; the slicers select 'Date'[Year] = 2025 and orders[channel] = web. The report already has Sales (completed orders only). Create a measure called All Channel Sales that returns Sales for every channel: it must ignore the channel slicer but still respect the year slicer and the category on each row.

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

Your measure

DAX
Visual
A matrix: rows = products[category]; values = [Sales], [All Channel Sales]
Slicers
'Date'[Year] = 2025orders[channel] = web
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[All Channel Sales] fills in when you press Check
    categorySalesAll Channel Sales
    Decor121,360.48
    Furniture261,924.1
    Kitchen120,343.39
    Lighting156,002.69
    Outdoor219,252.66
    Textiles101,463.46
    Total980,346.78

    Mote checks your result

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

    The lesson

    A slicer looks like a control on the page, but to DAX it is simply a filter. Pick web and Power BI adds orders[channel] = "web" to the filter context of every visual the slicer reaches. Your measures don’t know a slicer exists; they only see fewer rows.

    What you are working in

    This is a practice report, not Power BI: Power BI Desktop runs only on Windows, so the page gives you the store’s model, a matrix, and the slicer selections written above it (year 2025, channel web). A DAX engine built for this site evaluates your measure in every cell with those filters on and compares each cell with the answer.

    To do it for real in Power BI Desktop: add a slicer from Visualizations → Slicer, drop orders[channel] into its Field well and pick web. Then select a table in the Data pane, Home → New measure, type the formula and drag the measure into the matrix’s Values.

    How a slicer reaches a measure

    For the Furniture row in 2025, [Sales] is evaluated with three filters: category = Furniture (the row), year = 2025 (a slicer, through the Date table) and channel = web (the other slicer). That’s $261,924. Every visual on the page gets the same slicer filters unless you change its interactions (level 3).

    Usually that’s exactly right. Sometimes one number must stay fixed while the reader clicks: a denominator for a share, a target line, “all channels” for comparison.

    Taking one filter away

    CALCULATE evaluates an expression in a modified filter context. Its filter arguments can add filters, replace them, or remove them:

    All Channel Sales = CALCULATE([Sales], REMOVEFILTERS(orders[channel]))

    REMOVEFILTERS(orders[channel]) removes any filter on that one column, whether it came from a slicer, a row header or the filter pane. The category row and the year slicer stay. On the Total row the measure gives $2,300,170: all of 2025, all channels.

    ALL(orders[channel]) used as a CALCULATE filter does the same thing; REMOVEFILTERS was added later as a clearer name for exactly this use.

    How far the removal reaches

    Remove the narrowest thing you mean:

    filter argument channel year category
    REMOVEFILTERS(orders[channel]) removed kept kept
    REMOVEFILTERS(orders) removed removed kept
    REMOVEFILTERS() (no argument) removed removed removed

    The middle row surprises people. The year filter starts on the Date table, but it reaches orders through the relationship, and clearing the whole orders table clears that too.

    The other way: change the visual, not the measure

    In Power BI you can also stop the slicer from touching a whole visual with Format → Edit interactions. That’s fine when every number in the visual should ignore it. When one column follows the slicer and the next one doesn’t, as here, only DAX can do it.

    Common mistakes

    • Removing the whole table. ALL(orders) silently drops the year as well.
    • Hard-coding the other channels. orders[channel] IN {"web", "app", "marketplace"} looks equivalent, but it replaces the filter; it breaks the day a fourth channel appears.
    • Forgetting the slicer in the title. If one column ignores a slicer, say so in its name.

    Cookie settings

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