Modelling customer-specific property groups

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 table

Relationships:

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.