Semantic model tricks: Show when measures don't have a relationship to the column you are showing it by
- DataZoe
- 12 minutes ago
- 12 min read
Have you ever put a measure on a visual with a column from a table and found it repeated the same value for every row and the total? This indicates there is no relationship for that measure and the column. And that simply may be the case, as in, there is no relationship to create. Let's look at how we can account for that in a different way, by showing a placeholder value such as ###.
In this post, I'll walk you through a pattern using a calculation group with a no selection expression that automatically shows ### when a measure is used with a dimension it's not related to. What's cool about this approach is it works without the report author or user having to do anything. And it comes with a way to break out of this behavior if I wanted to have the existing behavior on a report, page, or visual!
Let's say I have a simple retail model with three tables:
Products – 20 products with a Product ID, Product Name, Category, and Unit Price.
Sales – 20 transactions with a Sale ID, Product ID, Quantity, Sale Date, and Store.
Store Budgets – 18 rows of planned budget and headcount by Store and Month.
Products and Sales are connected through a many-to-one relationship on Product ID. Store budgets isn't related to the other tables, so there is no relationship available.
I created some basic measures. On the Sales side I have [Revenue], [Qty Sold], [Orders], and [Avg Revenue per Order]. On the Store Budgets side I have [Budget] and [Headcount]. And on Products I have [Avg Unit Price] and [Product Count].
Now if I create a table visual and put Product Name on the rows with all my measures, the Sales measures are good. But Budget and Headcount show the exact same total value on every single row. That's because Store Budgets has no relationship to Products. Power BI doesn't filter Store Budgets when you slice by Product Name, so every row just shows the grand total. That is correct from the semantic model perspective, but let's show a placeholder instead.
The same thing happens in reverse. If I put Store on the rows, [Budget] and [Headcount] look great per store. But [Revenue] and [Orders]? Same value on every row.
Let's have the semantic model itself to catch this and handle the output differently. If a measure is being used with a dimension that it has no relationship to, just show ### instead of a repeating the number. Then it works for all downstream scenarios -- report building, Explore, or any DAX query -- with selection expressions in calculation groups.
Calculation groups are a powerful feature in semantic models (compatibility level 1605 and above). If you've used them before, you've probably seen them used for time intelligence patterns like YTD, PY, YOY%. The basic idea is you define calculation items that modify any measure using SELECTEDMEASURE().
But there's a lesser-known property on calculation groups called the no selection expression. From the Microsoft documentation on calculation groups:
The noSelectionExpression on a calculation group will be applied if the calculation group has not been filtered. This is mostly used to perform default actions without the need for the user to take action while still providing flexibility to the user to override the default action.
This works for this scenario as it's always on by default. And if I add a "No Check" calculation item, users can explicitly select it to turn the check off.
One thing to note: as of now, you can only add selection expressions (no selection and multiple or empty selection) to a calculation group through TMDL view in Power BI Desktop or through external tools. The Power BI Desktop calculation group UI doesn't have a way to set these properties yet. So you'll need to switch to TMDL view to add or edit them.
Step 1: Create the calculation group
I created a calculation group called Relationship Check with a precedence of 10. It has one calculation item called No Check that simply passes the measure through.
-- No Check calculation item
-- Pass-through: returns the measure unchanged.
-- Use this item to explicitly disable the relationship check.
SELECTEDMEASURE()
Nothing fancy there. If someone puts "No Check" on a slicer or filter, the relationship checking is turned off and all measures return as they would before.
Step 2: The no selection expression
Here's where the magic happens. The no selection expression uses two key DAX functions together:
ISSELECTEDMEASURE() – Checks if the measure currently being evaluated is one of the measures I specify. This lets me identify whether the measure is a "Sales measure" or a "Budget measure."
ISINSCOPE() – Returns TRUE when a column is being used as a grouping column on the visual (like on the rows or columns of a table, or as an axis). Importantly, it returns FALSE at the total row, which is exactly the behavior we want!
Here is the full expression:
-- Identify which group the current measure belongs to
VAR _IsSalesMeasure =
ISSELECTEDMEASURE( [Revenue] )
|| ISSELECTEDMEASURE( [Qty Sold] )
|| ISSELECTEDMEASURE( [Orders] )
|| ISSELECTEDMEASURE( [Avg Revenue per Order] )
VAR _IsBudgetMeasure =
ISSELECTEDMEASURE( [Budget] )
|| ISSELECTEDMEASURE( [Headcount] )
-- Check if any column from Products is on the visual axis
VAR _ProductInScope =
ISINSCOPE( Products[Product ID] )
|| ISINSCOPE( Products[Product Name] )
|| ISINSCOPE( Products[Category] )
|| ISINSCOPE( Products[Unit Price] )
-- Check if any column from Store Budgets is on the visual axis
VAR _StoreInScope =
ISINSCOPE( 'Store Budgets'[Store] )
|| ISINSCOPE( 'Store Budgets'[Month] )
|| ISINSCOPE( 'Store Budgets'[Budget Amount] )
|| ISINSCOPE( 'Store Budgets'[Head Count] )
RETURN
IF(
( _IsSalesMeasure && _StoreInScope )
|| ( _IsBudgetMeasure && _ProductInScope ),
"###",
SELECTEDMEASURE()
)
Let me walk through what this does.
First, I figure out what kind of measure is being evaluated. Is it one of my Sales measures? Or one of my Budget measures? I check each measure explicitly with ISSELECTEDMEASURE(). You do need to list them all out, but that also means you have full control over which measures are included. You could also utilize INFO.VIEW.MEASURES() if you have a lot of measures to look for! (I did do that -- it's at the end of this article!)
Next, I check what columns are on the visual. If any column from the Products table is being used as a grouping column, ProductInScope is TRUE. Same idea for Store Budgets columns with StoreInScope. I included every visible column from each table so it catches any combination.
Then the logic is straightforward:
If it's a Sales measure and a Store Budgets column is in scope → show ### (no relationship exists!)
If it's a Budget measure and a Products column is in scope → show ### (no relationship exists!)
Otherwise → return the measure normally with SELECTEDMEASURE()
And because ISINSCOPE() returns FALSE at the total row, totals always show the real value. This is important! The total is still meaningful even when detail rows aren't, because the total is unfiltered.
Now let's see it in action.
If I have a table with Product Name on the rows and all my measures, the Sales measures show their correct values per product. But Budget and Headcount show ### for every product row. The total row at the bottom still shows the values for everything.
If I switch to a table with Store on the rows, now Budget and Headcount show correct values per store. And Revenue, Qty Sold, Orders? They show ###. Again, totals are fine.
And card visuals with no grouping column? Everything shows normally because no column is in scope.
How to extend this
When you add new measures to your model, you just need to add them to the appropriate ISSELECTEDMEASURE() check in the no selection expression. For example, if I add a [Profit] measure to the Sales table, I'd add:
VAR _IsSalesMeasure =
ISSELECTEDMEASURE( [Revenue] )
|| ISSELECTEDMEASURE( [Qty Sold] )
|| ISSELECTEDMEASURE( [Orders] )
|| ISSELECTEDMEASURE( [Avg Revenue per Order] )
|| ISSELECTEDMEASURE( [Profit] ) -- new!
If you add a new table with its own measures, you'd add a new IsNewTableMeasure variable and a new NewTableInScope variable, then add the appropriate condition to the IF() logic.
And if you add new columns to an existing table, add them to the corresponding _InScope check so they're caught too.
How I built this with Copilot and the Power BI MCP
This problem came up recently which is why I was working on it. I actually built this entire example using Copilot in VS Code with the Power BI semantic modeling MCP server connected to Power BI Desktop. The MCP server lets Copilot talk directly to the semantic model running in Desktop, so I didn't have to switch back and forth between tools.
I asked Copilot to create the three calculated tables with DATATABLE(), add the relationship between Sales and Products, create all the measures, set up the calculation group with the no selection expression, rename columns and measures, add descriptions, and even bump the compatibility level from 1600 to 1605 when we hit that requirement. All of that happened through the MCP connection, modifying the live model in Desktop in real time.
The iteration on the calculation group DAX was especially helpful this way. I could describe what I wanted, Copilot would update the expression, I'd flip to Desktop to check the visual, and then tell Copilot what to fix. We went through a few approaches (the failed attempts I mentioned above) before landing on the ISSELECTEDMEASURE() + ISINSCOPE() pattern, and being able to iterate that quickly made a big difference.
If you want to try this workflow, you need VS Code with Copilot and the Power BI semantic modeling MCP server. Then connect to a running Power BI Desktop instance and start chatting!
And yes, I had Copilot help me write it up for you too!
The full TMDL script
I thought of uploading the PBIX file, but in any new Power BI Desktop file you can build this by pasting this script into the TMDL view and running it. Give it a go :).
createOrReplace
model Model
culture: en-US
defaultPowerBIDataSourceVersion: powerBI_V3
discourageImplicitMeasures
sourceQueryCulture: en-US
dataAccessOptions
legacyRedirects
returnErrorValuesAsNull
table Products
lineageTag: 8bbc5b16-b605-46d2-a49b-e3c193473aed
/// The average price across all products in the catalog. Useful for understanding the general price point of the product assortment.
measure 'Avg Unit Price' =
-- Average unit price across all products in the Products dimension
AVERAGE( Products[Unit Price] )
formatString: $#,##0.00
lineageTag: 49642a22-c91e-43d0-a29d-eaa4da83ae2d
/// The total number of products available in the catalog. Use this to monitor the breadth of the product range.
measure 'Product Count' =
-- Total number of distinct products in the Products dimension
COUNTROWS( Products )
formatString: #,##0
lineageTag: 9b54e2a6-7a5e-4754-a141-477f0c7fe4f5
column 'Product ID'
lineageTag: 726eb8f3-af3c-4cd3-86bb-1b9f730ca67a
sourceColumn: [ProductID]
column 'Product Name'
lineageTag: 0a3bb473-ba83-470a-80ec-844f2a969ac4
sourceColumn: [ProductName]
column Category
lineageTag: 21517004-d4d8-4228-8f14-b981f56b3f6b
isNameInferred
sourceColumn: [Category]
column 'Unit Price'
lineageTag: ecd17420-1c8c-4a4f-847d-8922f983de8b
sourceColumn: [UnitPrice]
partition Products = calculated
mode: import
source =
DATATABLE(
"ProductID", INTEGER,
"ProductName", STRING,
"Category", STRING,
"UnitPrice", CURRENCY,
{
{ 1, "Whole Milk", "Dairy", 3.49 },
{ 2, "Cheddar Cheese", "Dairy", 5.99 },
{ 3, "Greek Yogurt", "Dairy", 4.29 },
{ 4, "Sourdough Bread", "Bakery", 4.99 },
{ 5, "Croissants 4-Pack", "Bakery", 3.79 },
{ 6, "Blueberry Muffins", "Bakery", 5.49 },
{ 7, "Chicken Breast", "Meat", 8.99 },
{ 8, "Ground Beef", "Meat", 6.49 },
{ 9, "Atlantic Salmon", "Seafood", 12.99 },
{ 10, "Bananas", "Produce", 0.59 },
{ 11, "Avocados 3-Pack", "Produce", 4.99 },
{ 12, "Baby Spinach", "Produce", 3.49 },
{ 13, "Orange Juice", "Beverages", 4.49 },
{ 14, "Sparkling Water 6-Pack", "Beverages", 5.99 },
{ 15, "Cold Brew Coffee", "Beverages", 6.99 },
{ 16, "Tortilla Chips", "Snacks", 3.29 },
{ 17, "Trail Mix", "Snacks", 7.49 },
{ 18, "Dark Chocolate Bar", "Snacks", 2.99 },
{ 19, "Pasta Sauce", "Pantry", 3.99 },
{ 20, "Olive Oil", "Pantry", 9.99 }
}
)
table Sales
lineageTag: f2a784ac-98e4-488e-b34f-60852bb72bfa
/// The total sales revenue generated across all transactions. Calculated by multiplying the quantity of each item sold by its unit price. Use this to track overall sales performance.
measure Revenue =
-- Total revenue calculated by multiplying quantity sold by unit price
-- Uses RELATED to pull UnitPrice from Products via the Sales[ProductID] → Products[ProductID] relationship
SUMX( Sales, Sales[Quantity] * RELATED( Products[Unit Price] ) )
formatString: $#,##0.00
lineageTag: 0f75fb76-8afd-42d3-8b82-81bfba3fe4e5
/// The total number of individual items sold across all orders. Use this to understand sales volume regardless of price.
measure 'Qty Sold' =
-- Total number of items sold across all transactions
SUM( Sales[Quantity] )
formatString: #,##0
lineageTag: 7f9cfc2f-d73c-4f35-aebd-c19e1c672c11
/// The total number of sales transactions recorded. Each row in the Sales table represents one order. Use this to track how many purchases were made.
measure Orders =
-- Count of all sales transactions (one row = one order)
COUNTROWS( Sales )
formatString: #,##0
lineageTag: a6da01ef-3e2f-4436-a603-bbea883799eb
/// The average revenue earned per order. Helps identify whether customers are making larger or smaller purchases over time.
measure 'Avg Revenue per Order' =
-- Average revenue per order, safely dividing to avoid divide-by-zero errors
DIVIDE( [Revenue], [Orders] )
formatString: $#,##0.00
lineageTag: c4404f44-e947-481f-bc60-7cc8ce11f429
column 'Sale ID'
lineageTag: 1b06c62b-437e-4ab7-b0ed-e352d65df2ed
sourceColumn: [SaleID]
column 'Product ID'
lineageTag: 328c90f2-765a-4dfb-9a02-4d22fe584b14
sourceColumn: [ProductID]
column Quantity
lineageTag: f664e3b7-1f3e-4c82-9912-41b7552a2c9e
isNameInferred
sourceColumn: [Quantity]
column 'Sale Date'
lineageTag: 86e05e25-16d1-49f1-b68f-ee329880a21f
sourceColumn: [SaleDate]
column Store
lineageTag: 012e447f-7d9a-4d02-bc83-e2c1382e15eb
isNameInferred
sourceColumn: [Store]
partition Sales = calculated
mode: import
source =
DATATABLE(
"SaleID", INTEGER,
"ProductID", INTEGER,
"Quantity", INTEGER,
"SaleDate", DATETIME,
"Store", STRING,
{
{ 1, 1, 2, "2025-01-05", "Downtown" },
{ 2, 3, 1, "2025-01-07", "Eastside" },
{ 3, 7, 3, "2025-01-10", "Downtown" },
{ 4, 10, 6, "2025-01-12", "Westfield" },
{ 5, 14, 2, "2025-01-15", "Eastside" },
{ 6, 2, 1, "2025-02-02", "Downtown" },
{ 7, 5, 4, "2025-02-08", "Westfield" },
{ 8, 9, 1, "2025-02-14", "Eastside" },
{ 9, 12, 3, "2025-02-20", "Downtown" },
{ 10, 18, 5, "2025-03-01", "Westfield" },
{ 11, 4, 2, "2025-03-05", "Eastside" },
{ 12, 15, 1, "2025-03-10", "Downtown" },
{ 13, 8, 2, "2025-03-15", "Westfield" },
{ 14, 20, 1, "2025-03-22", "Eastside" },
{ 15, 11, 3, "2025-04-01", "Downtown" },
{ 16, 6, 2, "2025-04-10", "Westfield" },
{ 17, 16, 4, "2025-04-18", "Eastside" },
{ 18, 13, 2, "2025-04-25", "Downtown" },
{ 19, 17, 1, "2025-05-03", "Westfield" },
{ 20, 19, 3, "2025-05-12", "Eastside" }
}
)
table 'Store Budgets'
lineageTag: cbefa532-aa36-45f7-83df-9f790906f773
/// The total planned budget amount across all stores and months. This comes from the StoreBudgets table which is not linked to sales data. Use this for budget planning and comparison against actuals.
measure Budget =
-- Total planned budget from StoreBudgets (disconnected table, no relationships)
SUM( 'Store Budgets'[Budget Amount] )
formatString: $#,##0.00
lineageTag: aa4ae26e-03f0-47e6-8d9c-57b348ed2c2e
/// The total number of staff across all stores and months. This comes from the StoreBudgets table which is not linked to sales data. Use this for workforce planning and capacity analysis.
measure Headcount =
-- Total staff headcount from StoreBudgets (disconnected table, no relationships)
SUM( 'Store Budgets'[Head Count] )
formatString: #,##0
lineageTag: 9c2d80d5-c28d-4b09-aadf-7e07aea00f71
column Store
lineageTag: 2640ed2a-f732-4877-89eb-3534660f7c73
isNameInferred
sourceColumn: [Store]
column Month
lineageTag: 0a75e6d8-5f2e-4b4c-97e8-f015c5bee195
isNameInferred
sourceColumn: [Month]
column 'Budget Amount'
lineageTag: c439357c-0b90-46a1-962c-116a429a59f2
sourceColumn: [BudgetAmount]
column 'Head Count'
lineageTag: 0dc32613-35dd-4a5f-93d3-ed9c3f53a76a
sourceColumn: [HeadCount]
partition StoreBudgets = calculated
mode: import
source =
DATATABLE(
"Store", STRING,
"Month", STRING,
"BudgetAmount", CURRENCY,
"HeadCount", INTEGER,
{
{ "Downtown", "Jan", 15000, 12 },
{ "Downtown", "Feb", 14500, 12 },
{ "Downtown", "Mar", 16000, 13 },
{ "Downtown", "Apr", 15500, 13 },
{ "Downtown", "May", 17000, 14 },
{ "Downtown", "Jun", 16500, 14 },
{ "Eastside", "Jan", 12000, 8 },
{ "Eastside", "Feb", 11500, 8 },
{ "Eastside", "Mar", 13000, 9 },
{ "Eastside", "Apr", 12500, 9 },
{ "Eastside", "May", 14000, 10 },
{ "Eastside", "Jun", 13500, 10 },
{ "Westfield", "Jan", 10000, 6 },
{ "Westfield", "Feb", 9500, 6 },
{ "Westfield", "Mar", 11000, 7 },
{ "Westfield", "Apr", 10500, 7 },
{ "Westfield", "May", 12000, 8 },
{ "Westfield", "Jun", 11500, 8 }
}
)
table 'Relationship Check'
lineageTag: a9b91836-9b54-4833-8c0e-4771d892cf39
/// Automatically validates that measures are used with related dimensions. When no item is selected, sales measures show ### if StoreBudgets columns are in scope, and budget measures show ### if Products columns are in scope. Select 'No Check' to disable.
calculationGroup
precedence: 10
noSelectionExpression =
-- Applied automatically when no calculation item is selected.
-- Checks if a measure is being used with an unrelated dimension
-- and returns ### to prevent misleading values.
--
-- Sales measures (Total Revenue, Total Quantity, Total Orders, Avg Revenue per Order)
-- relate to Products via ProductID but NOT to StoreBudgets.
-- Budget measures (Total Budget, Total Headcount)
-- live in StoreBudgets which is disconnected from Products.
-- Totals are never blocked because ISINSCOPE returns FALSE at the total row.
VAR _IsSalesMeasure =
ISSELECTEDMEASURE( [Revenue] )
|| ISSELECTEDMEASURE( [Qty Sold] )
|| ISSELECTEDMEASURE( [Orders] )
|| ISSELECTEDMEASURE( [Avg Revenue per Order] )
VAR _IsBudgetMeasure =
ISSELECTEDMEASURE( [Budget] )
|| ISSELECTEDMEASURE( [Headcount] )
VAR _ProductInScope =
ISINSCOPE( Products[Product ID] )
|| ISINSCOPE( Products[Product Name] )
|| ISINSCOPE( Products[Category] )
|| ISINSCOPE( Products[Unit Price] )
VAR _StoreInScope =
ISINSCOPE( 'Store Budgets'[Store] )
|| ISINSCOPE( 'Store Budgets'[Month] )
|| ISINSCOPE( 'Store Budgets'[Budget Amount] )
|| ISINSCOPE( 'Store Budgets'[Head Count] )
RETURN
IF(
( _IsSalesMeasure && _StoreInScope )
|| ( _IsBudgetMeasure && _ProductInScope ),
"###",
SELECTEDMEASURE()
)
calculationItem 'No Check' =
-- Pass-through: returns the measure unchanged.
-- Use this item to explicitly disable the relationship check.
SELECTEDMEASURE()
column 'Check Type'
dataType: string
lineageTag: 8319385b-21a9-4806-a56e-599782a9928e
partition 'Partition_Relationship Check' = calculationGroup
relationship SalesToProducts
fromColumn: Sales.'Product ID'
toColumn: Products.'Product ID'
cultureInfo en-US
annotation __PBI_TimeIntelligenceEnabled = 1
annotation PBI_ProTooling = ["MCP-PBIModeling","TMDLView_Desktop"]I did get it to work with INFO.VIEW.MEASURES() too:
createOrReplace
table 'Relationship Check'
lineageTag: a9b91836-9b54-4833-8c0e-4771d892cf39
/// Automatically validates that measures are used with related dimensions. When no item is selected, sales measures show ### if StoreBudgets columns are in scope, and budget measures show ### if Products columns are in scope. Select 'No Check' to disable.
calculationGroup
precedence: 10
noSelectionExpression =
-- Applied automatically when no calculation item is selected.
-- Dynamically determines which table the current measure belongs to
-- using INFO.VIEW.MEASURES(), then checks if an unrelated dimension
-- is in scope using ISINSCOPE().
--
-- Measure table lookup is dynamic: new measures are handled automatically.
-- ISINSCOPE checks are explicit: new columns must be added manually.
-- Totals are never blocked because ISINSCOPE returns FALSE at the total row.
-- Dynamically find which table the current measure belongs to
VAR _MeasureTableName =
MAXX(
FILTER(
INFO.VIEW.MEASURES(),
[Name] = SELECTEDMEASURENAME()
),
[Table]
)
-- Classify the measure by its relationship group
-- Sales and Products are related to each other, Store Budgets is disconnected
VAR _IsSalesOrProductsMeasure =
_MeasureTableName IN { "Sales", "Products" }
VAR _IsBudgetMeasure =
_MeasureTableName = "Store Budgets"
-- Check if any column from Products is on the visual axis
VAR _ProductInScope =
ISINSCOPE( Products[Product ID] )
|| ISINSCOPE( Products[Product Name] )
|| ISINSCOPE( Products[Category] )
|| ISINSCOPE( Products[Unit Price] )
-- Check if any column from Store Budgets is on the visual axis
VAR _StoreInScope =
ISINSCOPE( 'Store Budgets'[Store] )
|| ISINSCOPE( 'Store Budgets'[Month] )
|| ISINSCOPE( 'Store Budgets'[Budget Amount] )
|| ISINSCOPE( 'Store Budgets'[Head Count] )
RETURN
IF(
( _IsSalesOrProductsMeasure && _StoreInScope )
|| ( _IsBudgetMeasure && _ProductInScope ),
"###",
SELECTEDMEASURE()
)
calculationItem 'No Check' =
-- Pass-through: returns the measure unchanged.
-- Use this item to explicitly disable the relationship check.
SELECTEDMEASURE()
column 'Check Type'
dataType: string
lineageTag: 8319385b-21a9-4806-a56e-599782a9928e
partition 'Partition_Relationship Check' = calculationGroup
Comments