Forum Discussion
Strange behaviour between tables with relationships - returning row combinations that do not exist
For context: I have set up medallion architecture using MS Fabric and created a custom sematic model from my lakehouse and am now developing reports.
I've encountered behaviour within a table that does not make any sense to me. I have 2 tables; Project and Contract where there is a 1 to * relatonship between Project and Contract (one project has many contracts) with the two tables linking on project_id.
All very normal stuff, and I have this same set up in the older data model we have (prior to transitioning to Fabric). Where it gets strange (and honestly is pi**ing me off) is when i try to populate a table visual containing attributes from both tables.
The expected behaviour is (based on using the project_id 21448 from dim_project and the contract_no and contract_name from dim_contract) that if i put in the project_id from dim_project and the contract_no & contract_name from dim_contract that it will return the 4 contracts associated with that project and their names.
This IS how it behaves in the old model (same relationships) and in tests i recreate with the same lakehouse tables via a different semantic model.
So old model / test model (notibly both import mode): When i split them up and select the correct project_id the contract values filter correctly and provide the correct contract info. When combined into a single tabke it behaves as inteded (showing the project_id against all of the correct contracts
Current semantic model (DirectLake): When i split them up and select the correct project_id the contract values filter correctly and provide the correct contract info. But when i combine them it starts to create combinations that do not exist for contract numbers that do not exist for this project contract combination. Interestingly if i put the contract_id into the combined table it will work, but i shouldn't have to.
I also tested creating a brand new semantic model from the lakehouse with a directlake connection and just imported those 2 tables and it behaved correctly. So i am at a loss as to why this is occuring. It must be something to do with my model, but the only table that filters dim_contract is dim_project. I do have RLS implemented but even when i remove the roles it does not change
edit: I also tested deactivating all other relationships in order to recreate the successful scenario with the other direct lake semantic model where i import just the two tables in qestion.
- I have fixed this issue. I simply deleted all relationships and remade them. Most likely issue is that somewhere when setting up my relationships I have created something that was not correct and hadn't noticed. Since rebuilding all of the relationships it is behaving as expected. Classic PICNIC issue (Problem In Chair, Not In Computer)
3 Replies
- GilbertQ
Super User
So just to confirm, when you add the contract ID into the table it works as expected. If this is the case, this is because it needs the contract ID in there to complete the filterof propagation by the relationship.
Perhaps you're correct. But that is not required in my other scenarios where i recreated the set up with the two tables in question (see screeshow below):
In addition to that if i bring in a single value from contract e.g. just project_id and contract_no when using the problematic set up i get the correct values returned
If the problem was recreateable in other scenarios i'd perhaps understand but it isnt. Could this be connected to any of the Fabric outages? Is there perhaps a strange setting or quirk i've managed to turn on.
- I have fixed this issue. I simply deleted all relationships and remade them. Most likely issue is that somewhere when setting up my relationships I have created something that was not correct and hadn't noticed. Since rebuilding all of the relationships it is behaving as expected. Classic PICNIC issue (Problem In Chair, Not In Computer)
