Forum Discussion
Super UserHow to model relationships without bothdirectional filter?
I am modeling Azure EA data in Power BI.
My tables:
Fct_EA_AmortizedCosts → main cost fact
Dim_EA_AmortizedCosts_Resources → resource dimension
Dim_EA_AmortizedCosts_Tags
Relationships:
Dim_Resources[DateResourceIdKey] 1 → * Fct_Costs[DateResourceIdKey]
Dim_Resources[DateResourceIdKey] 1 → * Dim_Tags[DateResourceIdKey]
All relationships are single-direction from the dimension outward.
I want to build a visual that shows Tags per Subscription, like:
Subscription
Tag Key
Tag Value
DISTINCTCOUNT of ResourceId (from Tag dimension)
When I put fields into a matrix:
Subscription → from Fct_EA_AmortizedCosts
Key/Value → from Dim_EA_AmortizedCosts_Tags
I get the error:
“Power BI can’t determine the relationship between these fields.”
What is the correct data model so that:
I can slice by Subscription
I can show Tag Key/Value
And the distinct count of tagged ResourceIds works correctly?
Should I:
Use a Subscription from the dimension instead of the fact?
Add a separate Subscription dimension table?
Change relationship direction?
Or use a DAX workaround?
Best,
Jacek
Hi jaryszek,
The recommended approach is to introduce a dedicated Subscription dimension instead of taking Subscription directly from the fact table. This creates a clean filter path: Subscription → Cost Fact → Resources → Tags, all using single-direction relationships. With this structure, selecting a subscription properly filters the associated cost records, those cost records filter the resources, and the resources filter the tags, enabling reporting such as Subscription, Tag Key, Tag Value, and distinct resource counts without requiring bidirectional filtering or connecting tags directly to costs.
Thanks,
prashanth
5 Replies
v-prasareCommunity Support
Hi jaryszek,
The recommended approach is to introduce a dedicated Subscription dimension instead of taking Subscription directly from the fact table. This creates a clean filter path: Subscription → Cost Fact → Resources → Tags, all using single-direction relationships. With this structure, selecting a subscription properly filters the associated cost records, those cost records filter the resources, and the resources filter the tags, enabling reporting such as Subscription, Tag Key, Tag Value, and distinct resource counts without requiring bidirectional filtering or connecting tags directly to costs.
Thanks,
prashanth
- amitchandak
jaryszek ,
Make this relation Many to many, filter direction Dim_Tags to Dim_Resources, if you do not want bidirectional
Dim_Resources[DateResourceIdKey] 1 → * Dim_Tags[DateResourceIdKey]
- jaryszek
Thank you for workaround.
Now, what is the best practice for designing it to make this the best performence as possible?
Best,
Jacek- amitchandak
jaryszek , As this table is not connected with Fact, a bi-directional join should be fine, as per my understanding.
- jaryszek
Generally it is a more business model design issue.
1 Resouce can have many Tags related. But tags can not be connected to Cost table - this is not making sense from business point of view.
How can I model that to keep the best performance?
Best,
Jacek
