Yes. In DAX, a measure can return a result based on a column value: use CALCULATE to apply a condition such as a particular status, or use SELECTEDVALUE with IF or SWITCH to branch on the value currently visible in the report. Which pattern is right depends on whether the condition is fixed or should change with the visual’s filter context.
What it means to base a measure on a column
A measure is a calculation evaluated when a report queries the model. It responds to filters and groupings in the visual, so the same measure can return different results for different products, dates, regions, or statuses. A column, by contrast, stores a value for each row. A measure does not automatically run once for every row in the way a calculated column does.
That distinction matters because a column can contain many values in the current report context. A measure that refers directly to Sales[Status] may therefore have no single status to use. DAX needs either a rule that filters rows, or a way to turn the visible column values into one scalar value. Common tools are CALCULATE, SELECTEDVALUE, and iterators such as SUMX.
For the examples below, assume a table named Sales has columns Amount and Status, and that the model has a product table named Product with a Category column. Create measures in Power BI Desktop using the Modeling tab’s New measure command, or in Excel Power Pivot using its measure tools. The DAX expressions themselves are model formulas, not worksheet formulas.
Use CALCULATE for a fixed column condition
If the measure should always calculate a value for a specific column value, start with a base measure and filter it. This is usually the clearest pattern for questions such as “What are sales for approved orders?”
Total Sales = SUM(Sales[Amount])
Approved Sales =
CALCULATE(
[Total Sales],
Sales[Status] = "Approved"
)
[Total Sales] sums the amount column in the current filter context. CALCULATE evaluates that measure with an additional status condition. If the approved rows total 8,400, the measure returns 8,400 in a card with no other filters; a date or region slicer can still narrow the calculation.
For a direct filter on one column, the Boolean condition shown here is concise and valid DAX. If the intent is to keep only rows already selected by a filter on Status, rather than replace that column’s existing filter, use KEEPFILTERS:
Approved Sales Within Selection =
CALCULATE(
[Total Sales],
KEEPFILTERS(Sales[Status] = "Approved")
)
This difference can affect a report where a user selects “Pending” in a Status slicer. The first measure applies Approved as its Status filter; the second intersects Approved with the existing selection, which can produce a blank result when the selection excludes Approved.
Branch on the value currently selected
Use SELECTEDVALUE when the answer should depend on the single column value in the current context. It returns that value when exactly one distinct value is visible. If zero or multiple values are visible, it returns blank by default, or an alternate result you provide.
Category Target =
VAR CurrentCategory = SELECTEDVALUE(Product[Category])
RETURN
SWITCH(
CurrentCategory,
"Bikes", 50000,
"Accessories", 12000,
"Clothing", 8000,
BLANK()
)
Place Category on the rows of a table visual and add this measure. The Bikes row returns 50,000, Accessories returns 12,000, and Clothing returns 8,000. A grand-total row may show blank because it contains several categories rather than one. That is not necessarily an error; it is the natural result of asking for a single category-specific target when the total has multiple categories.
If a total should display a message or a deliberate fallback, provide an alternate result to SELECTEDVALUE, such as SELECTEDVALUE(Product[Category], "Multiple or none"), and handle that alternate value in the measure. Use a numeric fallback only when it makes sense for the measure’s result. A text result cannot be added like a numeric target, so keep the return values compatible with the intended use.
Choose between IF and SWITCH
IF is convenient for one condition with two outcomes. For example, a measure could apply a higher rate to one selected category and a lower rate to all others:
Category Rate =
IF(
SELECTEDVALUE(Product[Category]) = "Bikes",
0.10,
0.05
)
This returns 10% for Bikes and 5% otherwise, including when the category is not uniquely determined. That fallback behavior may be what you want, but it can also hide ambiguous contexts. If multiple categories should not receive the default rate, check for a single selection explicitly or use SELECTEDVALUE with a clear alternate case.
For several named values, SWITCH is easier to read than a long chain of nested IF functions. Add a default result at the end if unmatched values need a defined answer. Use BLANK() when no result is appropriate; blanks are often preferable to zero because a zero can imply that a meaningful calculation was performed and returned nothing.
When a row-by-row calculation is necessary
Sometimes the requirement is to inspect each row, apply a condition, and then add the qualifying amounts. For example, a measure might total approved amounts using an iterator:
Approved Sales Row by Row =
SUMX(
Sales,
IF(Sales[Status] = "Approved", Sales[Amount], 0)
)
SUMX evaluates its expression for each row in the Sales table supplied to it, then sums the results. For this simple status filter, CALCULATE([Total Sales], Sales[Status] = "Approved") is generally shorter and more direct. The iterator pattern is useful when the expression genuinely needs a row-level calculation before aggregation, such as multiplying quantity by a row-specific rate or applying several row conditions.
Keep the table argument to an iterator in mind. SUMX(Sales, ...) iterates the rows of Sales that are visible under the current context. It does not automatically mean every row in the entire model. Filters from dates, products, relationships, and report selections can affect which rows are considered.
Understand filter context and totals
Measures are evaluated in filter context: the set of filters supplied by slicers, page filters, visual rows and columns, and DAX functions. A product category on a table visual gives each row a different context. A card usually has a broader context because it has no category row, though slicers can still filter it.
This explains why a measure that works on individual rows can behave differently at a total. DAX typically recalculates a measure in the total’s context; it does not simply add the displayed row results. For example, SELECTEDVALUE(Product[Category]) can return a category on each detail row but blank for a total spanning several categories. If the desired total is the sum of category-specific results, write a measure that iterates categories and sums the calculation, rather than expecting the single-category branch to add itself.
Also distinguish a filter condition from a value lookup. CALCULATE changes the context in which an existing measure is evaluated. SELECTEDVALUE reads a scalar value from the current context. They solve related but different problems, and choosing between them is often the key to making a measure behave as expected.
Measure or calculated column?
Use a measure when the answer should respond to report filters and be aggregated dynamically. A sales total, conditional total, percentage, or category-dependent target is often a measure. Measures do not store a separate result for every table row; they are calculated as needed in the context of a visual.
Use a calculated column when you need a persistent row-level result, for example a status label derived once per transaction and then used for grouping or filtering. A calculated column is evaluated for rows during model processing or refresh and stored in the model. It generally does not recalculate in response to a slicer. A calculated column can be useful as a classification, but it is not a substitute for a dynamic measure.
For a simple conditional sum, prefer a measure using CALCULATE rather than adding a helper column solely to mark qualifying rows. A helper column may still be justified when the row classification is reused in many places, needs to be visible as a field, or has a clear modeling purpose.
Common errors and how to fix them
- “A single value for column cannot be determined.” The measure is trying to use a column that has multiple visible values as though it were one value. Use
SELECTEDVALUEwhen a single value is required, or use an aggregation or filter-based calculation if the question concerns many rows. - The result is blank on the total row. Check whether the total contains multiple values and whether
SELECTEDVALUEtherefore returns blank. Decide what the total should mean, then add explicit total logic if needed. - The condition seems to ignore a slicer.
CALCULATEcan replace an existing filter on the same column. UseKEEPFILTERSwhen the condition should intersect with that selection, or inspect the other filters affecting the measure. - The number is repeated for every category. Confirm that the category field in the visual comes from the intended related table and that relationships in the model allow its filter to reach the fact table. A missing or inactive relationship can prevent the expected filter flow.
- Text and numbers are mixed in the result. Keep the possible return values consistent. A measure intended for numeric aggregation should return numbers or blank, not a mix of numeric values and explanatory text.
Frequently Asked Questions
Can a DAX measure use an IF statement with a column?
Yes, if the column resolves to one value in the current context, often through SELECTEDVALUE. If the condition applies to many rows, use a filter-based calculation or an iterator instead of an unqualified column reference.
How do I return a value for each category in a visual?
Put the category field on the visual and create a measure that reads the current category with SELECTEDVALUE, then uses SWITCH or IF to return the corresponding result. Decide separately what the grand total should show.
Should I use CALCULATE or SELECTEDVALUE?
Use CALCULATE to evaluate a measure under a filter, such as sales for Approved rows. Use SELECTEDVALUE to retrieve one currently visible column value and branch on it. Some measures use both, but they serve different purposes.
Can a measure return text based on a column value?
Yes. A measure can use SWITCH or IF to return text for display, provided the result makes sense for the visual. A text measure is not suitable for numeric summing or other arithmetic aggregation.
Why does SELECTEDVALUE return blank?
By default, it returns blank when the context has no single distinct value—for example, on a total covering several categories. Add an alternate result if appropriate, or change the calculation to handle multiple values explicitly.
Related
- Excel Shortcut Mouse Pad: What to Look For
- Excel Shortcut to Move Between Tabs: Windows and Mac
- Workbook Almost Too Large? How to Shrink an Excel File
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Multi-Criteria XLOOKUP in Google Sheets: How to Use ItIn Google Sheets, use XLOOKUP with multiplied condition tests to find a row matching more than one criterion.…
- Excel AutoFit Column Width Shortcut and How It WorksIn Excel for Microsoft 365 on Windows, select the column or columns, then press Alt+H, O, I in…
- Lock Cells in Google Sheets: Protect a RangeTo lock cells in Google Sheets, select the cells, choose Data > Protect sheets and ranges, then set…
- Remove Formatting Marks in Word: Show or Hide ThemTo remove formatting marks from view in Word, press Ctrl+Shift+8 on Windows or Command+8 on Mac, or select…
- How to Calculate an Average in an Excel ColumnIf your numbers are in B2:B10, select an empty cell and enter =AVERAGE(B2:B10), then press Enter. Excel returns…
- Can You Use Solver in Excel for the Web? What WorksNo: Microsoft’s Solver add-in cannot be run in Excel for the web, so you can’t set up or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.