Forum Discussion
Modelling customer-specific property groups
Hi,
I’m struggling to work out the correct relationships in Power BI to link my Property Group dimension to my Records fact table.
My data:
- Each record relates to a single customer and a single property.
- Each customer can assign property groups to properties.
- A property can belong to multiple property groups for the same customer.
- The same property can be assigned to different property groups by different customers. These groupings are customer-specific.
I want to be able to analyse my records by property group.
I tried using a compound key of clientID and PropertyID to relate property groups to records but this is a many-many relationship which makes me think I am misunderstanding something.
I have attached some dummy data to illustrate the problem. Not sure if I have explained this very well but any help is appreciated.
Hi DomBateman,
You're not misunderstanding: this really is many-to-many, because one (client, property) pair can sit in several groups. The clean way to model it is a bridge table, with a small dimension on the (client, property) key in between, instead of relating the groups directly to the fact table.
PropertyGroup (GroupID, GroupName, ClientID) 1 | * ClientPropertyGroup (CPKey, GroupID) <- bridge: one row per assignment * | ClientProperty (CPKey, ClientID, PropertyID) 1 <- unique per client+property | * Records (RecordID, ClientID, PropertyID, CPKey, ...)Build the key the same way in both tables (Power Query or a calculated column):
// Records and ClientPropertyGroup CPKey = Text.From([ClientID]) & "|" & Text.From([PropertyID]) // ClientProperty dimension = distinct CPKey/ClientID/PropertyID // from the union of Records and the assignment tableRelationships:
ClientProperty[CPKey] 1 -> * Records[CPKey] (single) ClientProperty[CPKey] 1 -> * ClientPropertyGroup[CPKey] (BOTH directions) PropertyGroup[GroupID] 1 -> * ClientPropertyGroup[GroupID] (single)The bidirectional filter on the bridge lets a group selection flow PropertyGroup → bridge → ClientProperty → Records. If you'd rather keep every relationship single-direction, leave it single and switch it on only inside the measure:
Records by Group = CALCULATE ( COUNTROWS ( Records ), CROSSFILTER ( ClientPropertyGroup[CPKey], ClientProperty[CPKey], BOTH ) )One thing to expect: because a property can be in several groups, the per-group rows won't add up to the total (a record counts once in each of its groups). The total itself is correct (distinct records), which is the right behaviour for this kind of grouping.
So my overall approach would be:
CPKey = ClientID|PropertyID in Records and assignments -> ClientProperty dimension (unique CPKey) -> bridge ClientPropertyGroup between it and PropertyGroup -> filter through the bridge (bi-di or CROSSFILTER in measures)AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
2 Replies
jubinsoniContinued Contributor
Hi DomBateman,
You're not misunderstanding: this really is many-to-many, because one (client, property) pair can sit in several groups. The clean way to model it is a bridge table, with a small dimension on the (client, property) key in between, instead of relating the groups directly to the fact table.
PropertyGroup (GroupID, GroupName, ClientID) 1 | * ClientPropertyGroup (CPKey, GroupID) <- bridge: one row per assignment * | ClientProperty (CPKey, ClientID, PropertyID) 1 <- unique per client+property | * Records (RecordID, ClientID, PropertyID, CPKey, ...)Build the key the same way in both tables (Power Query or a calculated column):
// Records and ClientPropertyGroup CPKey = Text.From([ClientID]) & "|" & Text.From([PropertyID]) // ClientProperty dimension = distinct CPKey/ClientID/PropertyID // from the union of Records and the assignment tableRelationships:
ClientProperty[CPKey] 1 -> * Records[CPKey] (single) ClientProperty[CPKey] 1 -> * ClientPropertyGroup[CPKey] (BOTH directions) PropertyGroup[GroupID] 1 -> * ClientPropertyGroup[GroupID] (single)The bidirectional filter on the bridge lets a group selection flow PropertyGroup → bridge → ClientProperty → Records. If you'd rather keep every relationship single-direction, leave it single and switch it on only inside the measure:
Records by Group = CALCULATE ( COUNTROWS ( Records ), CROSSFILTER ( ClientPropertyGroup[CPKey], ClientProperty[CPKey], BOTH ) )One thing to expect: because a property can be in several groups, the per-group rows won't add up to the total (a record counts once in each of its groups). The total itself is correct (distinct records), which is the right behaviour for this kind of grouping.
So my overall approach would be:
CPKey = ClientID|PropertyID in Records and assignments -> ClientProperty dimension (unique CPKey) -> bridge ClientPropertyGroup between it and PropertyGroup -> filter through the bridge (bi-di or CROSSFILTER in measures)AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.
Thank you this is really helpful. One question, do I then relate my Client dimension and Property Dimension to my new ClientProperty Dimension? Or do I leave them relating directly to the fact table?
