Forum Discussion
Modelling
Hi all,
I’m working with a semantic model provided by our data engineers and trying to extend it using a composite model in Power BI.
Here’s the setup:
I’m importing a dimension table I created myself, called ESG.
In ESG, there’s a column called CatNam.
The same column (CatNam) also exists in another dimension, MaterialDim, which is already connected to the FactTable (but through other keys/columns).
The issue:
When I try to create measures, the FactTable and the ESG table don’t seem able to “talk” to each other via the MaterialDim bridge. Relationships aren’t flowing as I expected.
Has anyone dealt with this kind of setup using semantic models and composite modeling? Is there a recommended way to get FactTable ↔ MaterialDim ↔ ESG working together?
Thanks in advance for any guidance!
Hi FabianHolm seems like the relationship beteen MaterialDim to FactTable is not set properly. As its a dimension and Fact table relationship if possible try to make it 1 to many.
If i implement your model with same sample data it will be look like this
Here: Relationships are
ESG[CatNum] = MaterialDim[CatNum]
MaterialDim[Key] = FactTable[Key]
Measure:
MyVal1 = CALCULATE(SUM(FactTable[Val1]), FILTER(ESG, ESG[Name] = "Cat1"))Results:
As you can see i got 13 (11 + 2) for Cat1 which is correct.
Idea:
- Keep 1-1 relationship between ESG and MaterialDim
- Make sure MaterialDim (Key) column is unique and relationship is 1 to many (ideal)
Let me know what is the different situation you have?
Thanks
6 Replies
Hi FabianHolm seems like the relationship beteen MaterialDim to FactTable is not set properly. As its a dimension and Fact table relationship if possible try to make it 1 to many.
If i implement your model with same sample data it will be look like this
Here: Relationships are
ESG[CatNum] = MaterialDim[CatNum]
MaterialDim[Key] = FactTable[Key]
Measure:
MyVal1 = CALCULATE(SUM(FactTable[Val1]), FILTER(ESG, ESG[Name] = "Cat1"))Results:
As you can see i got 13 (11 + 2) for Cat1 which is correct.
Idea:
- Keep 1-1 relationship between ESG and MaterialDim
- Make sure MaterialDim (Key) column is unique and relationship is 1 to many (ideal)
Let me know what is the different situation you have?
Thanks
Hi FabianHolm - Create a relationship ESG → MaterialDim
Make sure ESG[CatNam] and MaterialDim[CatNam] have the same cleaned values.
In the model, drag a relationship between them (likely many-to-one, ESG → MaterialDim).
Then, relationships will flow:
FactTable → MaterialDim → ESG.This works if you want ESG categories to filter down into FactTable.
try the above logic, and let us know.
Hi FabianHolm,
Double check that your relationships are set up correctly, and try to avoid many to many relatioships in this scenario.
If the relatioships are set up correctly, this should be functioning as you expect. If ti is still not, can you send us a screenshot of your model so we can see relationships?
If you found this helpful, consider giving some Kudos. If I answered your question or solved your problem, please mark this post as the solution.
Hi FabianHolm
Thank you for submitting your question to the Microsoft Fabric Community Forum, and thanks to rajendraongole1 , Royel and tayloramy for offering helpful suggestions.
Could you let us know if the suggested solution resolved your issue?If you still need help, please share more details so we can assist you further.
Thank you.
Hi FabianHolm
I wanted to check if you’ve had a chance to review the information provided. If you have any further questions, please let us know. Has your issue been resolved? If not, please share more details so we can assist you further.
Thank You.Hi FabianHolm
Just checking in as we haven't received a response to our previous message. Were you able to review the information above? Let us know if you have any additional questions.
Thank You.
