Relationship between calculated table and Direct Query table not recognized

[shadowsong42]

I'm trying to return TRUE/FALSE based on whether there is a matching row in a related table, and I'm encountering problems.

There is a fact table in my Direct Query data source:

Stores ISP (ProductID, SubsidiaryID, SnapshotDate, StoresReplenish, more)

There are two dim tables in my Direct Query data source:

Product (ProductID, SKU, more)

Subsidiary (SubsidiaryID, Subsidiary, more)

I have two calculated tables:

ReplenishAll = 
DISTINCT ( 
    SELECTCOLUMNS ( 
        'Stores ISP', 
        "ProductID", [ProductID], 
        "SubsidiaryID", [SubsidiaryID], 
        "Concat", CONCATENATE ( [ProductID],[SubsidiaryID] )
    ) 
)

ReplenishOnly = 
DISTINCT (
    SELECTCOLUMNS (
        FILTER (
            'Stores ISP',
            'Stores ISP'[Stores Replenish] > 0
                && 'Stores ISP'[SnapshotDate] > TODAY () - 8
        ),
        "ProductID", 'Stores ISP'[ProductID],
        "SubsidiaryID", 'Stores ISP'[SubsidiaryID], 
        "Concat", CONCATENATE ( [ProductID],[SubsidiaryID] )
    )
)

I created an active one-to-one relationship between the two tables on [Concat].

I created a third table:

Replenishable = 
ADDCOLUMNS (
	ReplenishAll,
	"Replenish", NOT ( ISBLANK ( RELATED ( 'ReplenishOnly'[Concat] ) ) )
)

I created active many-to-one relationships between Replenishable & Product and Replenishable & Subsidiary on the appropriate ID fields.

However, these relationships don't seem to be working. When I put replenish in a slicer, filtering a matrix that includes fields from Product and Subsidiary, it doesn't actually filter the matrix. When I double checked the relationships by using NATURALINNERJOIN ( 'Replenishable', 'Product' ) I got an error saying there were no column join columns detected.

Here's a screenshot showing the active relationship:

Why is the relationship I created not working? How can I fix it?

[shadowsong42 · Accepted solution]

I have discovered that relationships are broken in some way between Direct Query tables and calculated tables, so I am unable to use the calculated table measure as a slicer to filter Direct Query data in the visuals.

While it is possible that converting or duplicating some of the Direct Query tables to Import mode would solve the broken relationship issue, the underlying problem I was trying to solve was to highlight SKU&Subsidiaries incorrectly marked as not replenishable. Since I am having so much trouble with the slicer, I have decided to focus on the more correct solution to this underlying problem, which is to work with the upstream data support teams to fix the source data.

[v-saisrao-msft]

Hi @shadowsong42,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Based on your setup, it appears the problem stems from how filter context is being handled between your calculated tables and DirectQuery-based dimension tables. Relying on a concatenated key (Concat) and using RELATED () in this scenario can lead to inconsistent filter propagation especially when expecting slicer interactions to flow through these custom relationships. 

Try moving away from using a calculated column to determine the replenishment flag and instead implementing a DAX measure that dynamically evaluates replenishment status based on your original Stores ISP fact table. 

Is Replenishable = 
CALCULATE (
    COUNTROWS ( 'Stores ISP' ),
    'Stores ISP'[Stores Replenish] > 0,
    'Stores ISP'[SnapshotDate] > TODAY() - 8
) > 0

can be used to determine whether a given ProductID and SubsidiaryID pair had replenishment activity in the past 7 days. Since your fact table already has relationships with the Product and Subsidiary dimension tables, this measure will respect existing relationships and propagate correctly in your visuals. 

This approach avoids the limitations of calculated tables in composite models and ensures that slicers and visuals interact as expected.

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you.

[shadowsong42]

I was originally using a calculated measure! Unfortunately, my goal is to use the Replenishable field in a slicer, which I can't do with a calculated measure. Is there a way to generate this value that will return TRUE/FALSE and can be used in a slicer?

[v-saisrao-msft]

Hi @shadowsong42,

Thanks for the clarification—that makes your goal crystal clear. Since you’re aiming to use a Replenishable field in a slicer, a calculated measure won’t work, as slicers only operate on columns, not dynamic measures. You're absolutely right to pivot toward a solution that gives you a TRUE/FALSE value as a column, while still working within the constraints of your composite model and Direct Query setup. 

A reliable way to achieve this is to create a calculated table that builds the Replenishable status based on actual (ProductID, SubsidiaryID) key combinations from your fact table. Instead of relying on concatenated keys or RELATED(), which can cause filter propagation issues in composite models, you can use LOOKUPVALUE() to directly check whether a given combination appears in your filtered set. This ensures compatibility with slicers and avoids the inconsistency you're seeing. 

Replenishable =
SUMMARIZE(
    ReplenishAll,
    ReplenishAll[ProductID],
    ReplenishAll[SubsidiaryID],
    "Replenish",
    IF(
        NOT ISBLANK(
            LOOKUPVALUE(
                ReplenishOnly[ProductID],
                ReplenishOnly[ProductID], ReplenishAll[ProductID],
                ReplenishOnly[SubsidiaryID], ReplenishAll[SubsidiaryID]
            )
        ),
        TRUE,
        FALSE
    )
)

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly. 

Thank you. 

[shadowsong42]

I was hoping SUMMARIZE instead of SELECTCOLUMNS would solve the problem, since it preserves data lineage, but it's still not working.

Here's the new code for the three tables:

ReplenishOnly = 
SUMMARIZE (
	FILTER (
		'Stores ISP',
		'Stores ISP'[Stores Replenish] > 0
			&& 'Stores ISP'[SnapshotDate] > TODAY () - 8
	),
	'Stores ISP'[ProductID], 
	'Stores ISP'[SubsidiaryID]
)

ReplenishAll = 
SUMMARIZE (
	'Stores ISP', 
	'Stores ISP'[ProductID], 
	'Stores ISP'[SubsidiaryID]
)
	
Replenishable = 
ADDCOLUMNS (
	'ReplenishAll',
	"Replenishable",
	IF (
		NOT ISBLANK (
			LOOKUPVALUE (
				ReplenishOnly[ProductID],
				ReplenishOnly[ProductID], ReplenishAll[ProductID],
				ReplenishOnly[SubsidiaryID], ReplenishAll[SubsidiaryID]
			)
		),
		TRUE,
		FALSE
	)
)

Then I created a many-to-one relationship between Replenishable & Product and Replenishable & Subsidiary on the corresponding ID columns, and confirmed the relationship was active.

But the Replenishable slicer doesn't filter my results, and when I run NATURALINNERJOIN('Replenishable','Product') in Dax Studio, it tells me there are no common join columns detected.

(I also tried creating the last table using SUMMARIZE instead of ADDCOLUMNS but the results were the same.)

Why is this relationship still not working? Is it because the relationship is many to one, so Replenish can't filter Product?

[v-saisrao-msft]

Hi @shadowsong42,

Thanks for the additional clarification and updated code. You're very close—what you’re encountering now stems from how data lineage is handled within your calculated tables. While SUMMARIZE does preserve lineage, using functions like LOOKUPVALUE inside your Replenishable table breaks that lineage. Once lineage is lost, Power BI cannot propagate filters across relationships, which explains why your slicer isn’t filtering visuals based on fields from the Product or Subsidiary tables, and why NATURALINNERJOIN fails to detect join columns 

To resolve this while still achieving your goal of having a TRUE/FALSE column usable in a slicer, I suggest a lineage-preserving approach using set-based functions such as INTERSECT. These functions operate on full row context and retain lineage, allowing relationships to work correctly. You can define a variable using INTERSECT to find matching (ProductID, SubsidiaryID) pairs between ReplenishAll and ReplenishOnly, and then use ADDCOLUMNS to mark those as TRUE. 

If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.

Thank you. 

[shadowsong42]

Can you tell me more about how that would work? I can't find anything that explains how to get from the results of INTERSECT to returning TRUE if this row is in the intersect.

ReplenishOnly is a subset of ReplenishAll, so it seems like using INTERSECT on those tables would just return ReplenishOnly again.

[v-saisrao-msft]

Hi @shadowsong42,

Could you please provide some sample data so we can offer a solution to your issue?

Thank you