Forum Discussion

shadowsong42's avatar
shadowsong42
Resolver I
1 year ago
Solved

Relationship between calculated table and Direct Query table not recognized

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 (Prod...
Need Help
  • shadowsong42's avatar
    shadowsong42
    1 year ago

    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's avatar
v-saisrao-msft
Community Support
1 year ago

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.