You've built a measure that works perfectly in a table visual — until someone puts it in a card, or drills it into a matrix, or applies a slicer and suddenly you're dividing by zero, showing a nonsensical average, or spitting out a number that's technically correct but contextually wrong. Sound familiar? The root cause is almost always the same: your measure doesn't know what's happening in the filter context around it, so it can't adapt.
DAX information functions are the mechanism that lets your measures become context-aware. Instead of blindly calculating the same way regardless of how a report is filtered or arranged, your measures can inspect the current state of the model — "Is this column filtered? Is exactly one value selected? Is this measure being cross-filtered by something else?" — and respond accordingly. The result is measures that behave intelligently whether they're sitting in a summary card or a drill-down matrix, whether a slicer is in play or not.
By the end of this lesson, you'll be able to write production-ready measures that handle conditional logic based on filter context, avoid common display errors in multi-level visuals, and give end users a report experience that feels polished and intentional rather than fragile.
What you'll learn:
HASONEVALUE, ISFILTERED, and ISCROSSFILTERED — what they actually test and how they differ from each otherSELECTEDVALUE and VALUES alongside information functionsThis lesson assumes you're comfortable with:
SELECTEDVALUE and VALUES at a conceptual levelIf you're still building foundational DAX skills, complete the DAX Fundamentals and Filter Context lessons in this learning path first.
Before diving into the individual functions, let's establish the mental model. When any DAX measure evaluates, it exists inside a filter context — a set of filter constraints that determine which rows in your tables are "visible" during that evaluation. The filter context comes from multiple sources simultaneously:
CALCULATE filter arguments in your own codeYour measure doesn't automatically know which of these are in play. It just sees the resulting filtered table. Information functions let you interrogate that context before you calculate.
Think of them as the if(isempty(selection)) checks you'd write in any other programming language before doing something that could blow up — except in DAX, the "selection" is the entire filter context, and it can be surprisingly complex.
HASONEVALUE(<columnName>) returns TRUE when the current filter context results in exactly one distinct value being visible in the specified column. It's the DAX equivalent of asking: "After all filters have been applied, does this column have been narrowed down to a single value?"
HASONEVALUE(DimDate[Year])
This returns TRUE in three scenarios:
CALCULATE has reduced the column to one valueIt returns FALSE when multiple years are visible — or when zero values are visible, which is an edge case worth noting.
Imagine you're building a sales analytics model. You have a measure for total revenue and another for total units. A naive average selling price measure might look like:
Avg Selling Price (Naive) =
DIVIDE([Total Revenue], [Total Units Sold])
This works fine in most cases, but when you use it in a tooltip that appears at a summary level — say, the total row of a matrix — you get an average of averages, which is mathematically meaningless for many business questions. More specifically, imagine you want to show the effective price for the selected product, but only when a product is actually selected:
Effective Unit Price =
IF(
HASONEVALUE(DimProduct[ProductKey]),
DIVIDE([Total Revenue], [Total Units Sold]),
BLANK()
)
Now the card visual that shows this measure returns blank when the user hasn't selected a specific product, instead of showing a blended average across your whole catalog that looks like a real price but isn't one.
SELECTEDVALUE is the idiomatic shorthand for a common HASONEVALUE pattern. Understanding this relationship helps you choose the right tool.
-- These two are equivalent:
SELECTEDVALUE(DimDate[Year])
IF(
HASONEVALUE(DimDate[Year]),
VALUES(DimDate[Year]),
BLANK()
)
SELECTEDVALUE accepts an optional alternate result as a second argument:
SELECTEDVALUE(DimDate[Year], "Multiple Years")
Use SELECTEDVALUE when you just need the value itself (for display, for a CALCULATE filter, etc.). Use HASONEVALUE directly when you need the boolean result to branch your measure logic — typically inside a larger IF or SWITCH expression.
The most common misconception: developers assume HASONEVALUE being FALSE means "nothing is selected." It doesn't. FALSE means either multiple values are present or the column is completely unfiltered (all values visible). The distinction matters when you're building measures that need to behave differently between "user hasn't filtered yet" and "user filtered to multiple values."
Warning:
HASONEVALUEreturningFALSEdoes not mean the column is unfiltered. It means the column has anything other than exactly one visible value. An unfiltered column on a 10,000-row table returnsFALSEbecause it has 10,000 visible values.
ISFILTERED(<columnName>) returns TRUE when there is a direct filter applied to that specific column in the current context. "Direct" is the operative word here — a direct filter is one that lands on the column itself, either from a slicer that targets that column, a visual-level filter, or an explicit filter in CALCULATE.
ISFILTERED(DimDate[CalendarYear])
This gives you a different kind of information than HASONEVALUE. You're not asking "how many values are left?" You're asking "is there a filter actively constraining this column?"
Consider a matrix showing Sales by Region (rows) and Year (columns). In the "Total" row or "Total" column, you often want a fundamentally different calculation than in the detail rows. ISFILTERED is the right tool here.
Let's say your sales leadership wants to see market share percentage in detail cells, but they want absolute revenue in the subtotals because percentage across all regions doesn't mean anything:
Sales Display Measure =
IF(
ISFILTERED(DimGeography[RegionName]) && ISFILTERED(DimDate[CalendarYear]),
DIVIDE([Total Revenue], [Total Revenue All Regions]),
[Total Revenue]
)
When both Region and Year are filtered (meaning we're in a detail cell of the matrix), we show market share. When either is unfiltered (total row or total column), we show raw revenue. This is the kind of context-aware measure that makes reports feel like they were designed by someone who actually thought about the user experience.
These two functions test fundamentally different things. Here's how to think about which one to use:
| Scenario | Use |
|---|---|
| You need to know if a specific single item was selected | HASONEVALUE |
| You need to know if any filter at all is active on a column | ISFILTERED |
| You want to show a value only in "leaf" rows of a matrix | ISFILTERED on all hierarchy levels |
| You want to display the selected value in a title or label | SELECTEDVALUE (backed by HASONEVALUE) |
| You want different aggregation logic in totals vs. detail rows | ISFILTERED |
A common example that clarifies the difference: imagine a date hierarchy with Year > Quarter > Month. When you're in the "2023 Q2" row of a matrix:
ISFILTERED(DimDate[CalendarYear]) is TRUE (year is filtered to 2023)ISFILTERED(DimDate[QuarterNumber]) is TRUE (quarter is filtered to Q2)HASONEVALUE(DimDate[CalendarYear]) is TRUE (exactly one year visible)HASONEVALUE(DimDate[MonthNumber]) is FALSE (all three months of Q2 are visible)Tip: When working with hierarchies in matrices,
ISFILTEREDon each level of the hierarchy is how you detect which "level" of the hierarchy you're currently at in a given row or column header position.
ISCROSSFILTERED(<columnName>) returns TRUE when the column is being filtered — either directly or indirectly through relationships in the model. This is the crucial difference from ISFILTERED.
When a user applies a filter to one table, that filter propagates across relationships to related tables. If your Products table is filtered to "Electronics," and you have a relationship from Products to Sales, then the Sales table is cross-filtered — rows in Sales that don't relate to Electronics are excluded. ISCROSSFILTERED detects this situation.
ISCROSSFILTERED(FactSales[SalesAmount])
This returns TRUE if any column in any table that has a relationship path to FactSales (through which filtering can propagate) has an active filter.
Here's a realistic scenario. You're building a profitability dashboard. You have:
DimProduct with product categoriesDimCustomer with customer segmentsFactSales connected to bothA product category slicer cross-filters FactSales, but FactSales[SalesDate] itself has no direct filter on it. If you tested ISFILTERED(FactSales[SalesDate]), you'd get FALSE. But if you tested ISCROSSFILTERED(FactSales[SalesDate]), you'd get TRUE — because the date column is in a table that's being filtered via a relationship.
The practical use case: you want to show a baseline comparison only when the data is in its "natural" unfiltered state. Any external filter should suppress the baseline.
Baseline Revenue (Unfiltered Only) =
IF(
NOT ISCROSSFILTERED(FactSales[SalesAmount]),
[Total Revenue],
BLANK()
)
This measure returns blank the moment any filter touches the sales data, whether directly or through a relationship. Use this kind of guard when you want a "reference" metric to only display in a completely neutral context.
Here's a pattern that shows up frequently in production reports. Your team wants a measure that behaves differently when a customer segment slicer is active versus when it isn't — but the slicer is on DimCustomer, not on FactSales directly.
Segment-Aware Revenue =
IF(
ISCROSSFILTERED(FactSales[CustomerKey]),
-- A segment is active, show segment share
DIVIDE(
[Total Revenue],
CALCULATE([Total Revenue], REMOVEFILTERS(DimCustomer))
),
-- No segment active, show absolute revenue
[Total Revenue]
)
When the customer segment slicer is in use, the DimCustomer table is filtered, which cross-filters FactSales[CustomerKey] through the relationship. ISCROSSFILTERED picks that up, and the measure switches to showing segment share rather than absolute revenue.
Warning:
ISCROSSFILTEREDcan returnTRUEmore broadly than you might expect, especially in models with multiple relationship paths or bidirectional relationships. Test carefully with complex data models, because a column might be cross-filtered by something you didn't intend.
-- Returns TRUE only if there's a filter directly on this column
ISFILTERED(DimProduct[Category])
-- Returns TRUE if there's a direct filter on this column OR
-- if any table with a relationship path to this column is filtered
ISCROSSFILTERED(DimProduct[Category])
In most data models, ISCROSSFILTERED is a superset of ISFILTERED. If ISFILTERED is TRUE, ISCROSSFILTERED is also TRUE. But not vice versa.
Let's put these functions together in a realistic project. You're building a Sales Performance Dashboard for a retail company. The model has:
FactSales (SalesKey, DateKey, ProductKey, CustomerKey, SalesAmount, UnitsSold, CostAmount)DimDate (DateKey, Date, CalendarYear, QuarterName, MonthNumber, MonthName)DimProduct (ProductKey, ProductName, Category, Subcategory)DimCustomer (CustomerKey, CustomerName, Segment, Region)Total Revenue =
SUM(FactSales[SalesAmount])
Total Cost =
SUM(FactSales[CostAmount])
Total Units Sold =
SUM(FactSales[UnitsSold])
Gross Profit =
[Total Revenue] - [Total Cost]
Gross Margin % =
DIVIDE([Gross Profit], [Total Revenue])
Effective Selling Price =
IF(
HASONEVALUE(DimProduct[ProductKey]),
DIVIDE([Total Revenue], [Total Units Sold]),
BLANK()
)
This only shows a price when a single product is in context. In a product matrix, each row shows its actual selling price. In the total row, it shows blank rather than a meaningless blended rate.
Your matrix has a date hierarchy: Year > Quarter > Month. The finance team wants:
Transaction Count =
COUNTROWS(FactSales)
Margin Display =
SWITCH(
TRUE(),
-- Grand total: neither year nor month is filtered
NOT ISFILTERED(DimDate[CalendarYear]),
FORMAT([Gross Profit], "$#,##0"),
-- Year subtotal: year is filtered but month is not
ISFILTERED(DimDate[CalendarYear]) && NOT ISFILTERED(DimDate[MonthNumber]),
FORMAT([Transaction Count], "#,##0 txns"),
-- Detail cell: both year and month are filtered
ISFILTERED(DimDate[CalendarYear]) && ISFILTERED(DimDate[MonthNumber]),
FORMAT([Gross Margin %], "0.0%"),
-- Fallback
FORMAT([Gross Profit], "$#,##0")
)
Tip: The
SWITCH(TRUE(), ...)pattern is the idiomatic way to write multi-branch conditional logic in DAX. It evaluates each condition in order and returns the result of the firstTRUEmatch. Think of it as a readable chain ofIF/ELSE IFstatements.
This is a small measure that has a huge UX impact. Display a descriptive title in a card visual that updates based on what's filtered:
Report Context Title =
VAR SelectedYear = SELECTEDVALUE(DimDate[CalendarYear], "All Years")
VAR SelectedCategory = SELECTEDVALUE(DimProduct[Category], "All Categories")
VAR SelectedSegment = SELECTEDVALUE(DimCustomer[Segment], "All Segments")
RETURN
"Sales Performance: " & SelectedYear & " | " & SelectedCategory & " | " & SelectedSegment
When nothing is filtered, the card shows: "Sales Performance: All Years | All Categories | All Segments"
When the user selects 2023 and the Electronics category, it shows: "Sales Performance: 2023 | Electronics | All Segments"
Your executives want a YTD revenue measure, but they only want it to appear when the data isn't being sliced by segment — they consider segment comparisons a different analysis flow entirely:
YTD Revenue (Segment Neutral) =
IF(
NOT ISCROSSFILTERED(FactSales[CustomerKey]),
CALCULATE(
[Total Revenue],
DATESYTD(DimDate[Date])
),
BLANK()
)
When a customer segment slicer is active, this measure goes blank, and you can use that blank state to trigger conditional visibility of a different visual (via a bookmark or page navigation button).
Build the following in Power BI Desktop using your own data or a sample dataset like AdventureWorksDW:
Exercise 1: Context Detective Measure
Create a measure called Context Diagnostic that returns a text string describing the current filter context state. This is a debugging tool that you'll put in a card visual.
Context Diagnostic =
VAR YearFiltered = IF(ISFILTERED(DimDate[CalendarYear]), "Year: FILTERED", "Year: open")
VAR CategoryFiltered = IF(ISFILTERED(DimProduct[Category]), "Cat: FILTERED", "Cat: open")
VAR SalesCrossFiltered = IF(ISCROSSFILTERED(FactSales[SalesAmount]), "Sales: CROSS-FILTERED", "Sales: open")
VAR OneProduct = IF(HASONEVALUE(DimProduct[ProductKey]), "Single Product Selected", "Multiple Products")
RETURN
YearFiltered & " | " & CategoryFiltered & " | " & SalesCrossFiltered & " | " & OneProduct
Place this card on your report page alongside your slicers and a matrix. As you interact with slicers and select different cells in the matrix, watch this card update. This alone will build your intuition for how filter context flows through your model.
Exercise 2: Matrix-Level Adaptive Measure
Build a matrix visual with Product Category on rows, Calendar Year on columns. Create this measure:
Adaptive Metric =
SWITCH(
TRUE(),
HASONEVALUE(DimProduct[Category]) && HASONEVALUE(DimDate[CalendarYear]),
FORMAT(DIVIDE([Gross Profit], [Total Revenue]), "0.0%") & " margin",
HASONEVALUE(DimProduct[Category]),
FORMAT([Total Revenue], "$#,##0") & " revenue",
HASONEVALUE(DimDate[CalendarYear]),
FORMAT([Total Units Sold], "#,##0") & " units",
FORMAT([Total Revenue], "$#,##0") & " total"
)
Observe how the measure returns a completely different metric depending on where in the matrix it's rendered. The grand total cell, row subtotals, column subtotals, and detail cells all show different things.
Exercise 3: Slicer Influence Tracker
Add a Customer Segment slicer to your page. Create this measure and place it in a table that shows product categories:
Revenue with Segment Context =
IF(
ISCROSSFILTERED(FactSales[CustomerKey]),
[Total Revenue] & " (filtered)",
[Total Revenue] & " (all segments)"
)
Note: this will return a text string, which is for diagnostic purposes. Toggle the slicer on and off and observe that the text label changes even though no direct filter was applied to the products table or the date table. This demonstrates cross-filtering propagation.
The classic error: trying to detect the "total row" of a matrix using HASONEVALUE instead of ISFILTERED.
-- Wrong: This doesn't detect the total row
Bad Total Row Measure =
IF(
NOT HASONEVALUE(DimProduct[Category]),
[Total Revenue],
[Gross Margin %]
)
The problem: in the total row, HASONEVALUE returns FALSE because multiple categories are visible — which is correct behavior. But HASONEVALUE also returns FALSE in detail rows when you've added filters that show multiple values. You'll get false positives.
-- Right: Use ISFILTERED to test whether we're in a detail row
Good Total Row Measure =
IF(
NOT ISFILTERED(DimProduct[Category]),
[Total Revenue],
[Gross Margin %]
)
If you write ISCROSSFILTERED when you should use ISFILTERED, your condition triggers too broadly. Because most tables in a real model are connected through relationships, ISCROSSFILTERED will often be TRUE even when you haven't intentionally filtered anything — because some other filter in the report is flowing through a relationship path.
Diagnostic approach: Add your Context Diagnostic measure from Exercise 1 to the page when you're debugging unexpected measure behavior. See which columns report as FILTERED versus CROSS-FILTERED and trace which slicers or visual interactions are causing it.
A common pattern that causes unexpected behavior:
-- Problematic
Revenue If One Year =
CALCULATE(
[Total Revenue],
HASONEVALUE(DimDate[CalendarYear])
)
This won't work as intended. CALCULATE expects filter expressions or table expressions as its filter arguments, not boolean expressions derived from context inspection. HASONEVALUE inside CALCULATE doesn't add a filter — it evaluates to a scalar TRUE or FALSE which CALCULATE can't use as a filter predicate.
The right pattern is to keep HASONEVALUE in an IF outside the CALCULATE:
-- Correct
Revenue If One Year =
IF(
HASONEVALUE(DimDate[CalendarYear]),
CALCULATE([Total Revenue]),
BLANK()
)
HASONEVALUE tests the distinct values remaining after filtering, which is not always the same as "what the user clicked." Consider a table where the same year appears in multiple rows due to different granularities or a non-unique relationship. Even if the user has "selected" a single year in the slicer, if DimDate[CalendarYear] has a data quality issue with duplicates, HASONEVALUE could behave unexpectedly.
Always validate your dimension tables are clean (no duplicate values in the column you're testing) when HASONEVALUE returns surprising results.
Information functions that interrogate filter context — HASONEVALUE, ISFILTERED, ISCROSSFILTERED — are designed for measures. Using them in calculated columns will almost certainly return FALSE or unexpected results because calculated columns are evaluated at data refresh time, when there is no user-driven filter context.
-- This will always return FALSE in a calculated column
-- Don't do this
IsFilteredColumn = ISFILTERED(DimProduct[Category])
If you need conditional logic in a calculated column, it needs to be based on row-level data values, not filter context.
When an information-function-based measure behaves unexpectedly, work through this sequence:
Context Diagnostic measure as a card on the pageISFILTERED (direct filter), ISCROSSFILTERED (indirect), or HASONEVALUE (value count)ALLEXCEPT or REMOVEFILTERS in test measures to manually isolate context and confirm your hypothesisHASONEVALUE, ISFILTERED, and ISCROSSFILTERED themselves are extremely lightweight. They're simple boolean tests against the current filter context and add negligible overhead.
The performance concern comes from what you do because of them. A SWITCH(TRUE(), ...) structure that potentially triggers several different expensive sub-measures creates branching paths of execution. In most cases, DAX only evaluates the branch that returns TRUE, so you're not running all branches simultaneously — but complex nested conditional measures can still add up at scale.
If you're working with fact tables over 50 million rows and you're using ISFILTERED to swap between two entirely different complex aggregations, profile both aggregations independently first. The conditional logic itself is cheap; the underlying aggregations are where you'll hit performance walls.
SELECTEDVALUE(column) is internally optimized and is the preferred approach when you just need the value. Avoid writing your own IF(HASONEVALUE(...), VALUES(...), BLANK()) equivalent — it expresses the same intent with more code and potentially triggers two evaluations where SELECTEDVALUE would trigger one. Always prefer the built-in shorthand for the common case.
Here's what you've built understanding of in this lesson:
HASONEVALUE tests whether exactly one distinct value is visible in a column after all filters are applied. Use it when you need to gate a measure on a single-item selection, such as displaying a per-unit price only when one product is in context.
ISFILTERED tests whether a direct filter is actively constraining a specific column. Use it for total row detection in matrices, hierarchy level awareness, and distinguishing detail cells from summary cells.
ISCROSSFILTERED tests whether a column is filtered either directly or through relationship propagation. Use it when you need to detect whether any upstream filter — a slicer on a related table, for example — has affected the current calculation context.
The practical power of these functions isn't in using them in isolation — it's in combining them to make your measures context-aware. A measure that returns a meaningful metric at the right level of aggregation, shows blank instead of garbage numbers when the context doesn't support the calculation, and adjusts its behavior based on user interactions is the difference between a Power BI report that requires documentation and one that just works.
Where to go from here:
ISFILTERED, specifically designed for drill-down navigation in matrices. It's often more reliable than stacking ISFILTERED conditions for hierarchy level detection.ISCROSSFILTERED) behaves when you have inactive relationships you activate with CALCULATE.USERNAME(), USERPRINCIPALNAME(), and CUSTOMDATA() work in a similar "inspect-the-context" paradigm for building security-aware measures.The habit to build: any time you find yourself writing a measure that calculates something that only makes sense in certain contexts, stop and ask which information function is the right guard to put in front of it. That discipline will prevent a class of report bugs that are otherwise incredibly hard to track down.
Learning Path: DAX Mastery