Sometimes we face models which can’t be built because we fall into the circular relationship (which Power BI protects us against) or ambiguity (which sits there silently making all our results meaningless). I faced one of this situations the other day at work and found a workaround with –you guessed it– a calculation group.

The report is a fairly standard sales report. We have a Sales table,  then a product table, and a customer table. That works just fine until they tell you that they also want to report by «key account manager» which manages a group of products for a certain (large) customer. What is to be done?

Power BI will force you to keep one of the relationships inactive. The thing is that we want to filter by this new table, so we need both dimensions to filter simultaneously the sales table.

The solution is not that complex to figure out if you are familiar with TREATAS function. In this function you pass as argument a table with one or more columns and you specify a data lineage that should be applied to it. Is like «here is this product id list in a table I just built with some dax, please let’s make as if this selection comes from the product table». You get the idea. (or just check https://dax.guide/treatas/)

So, these Key account managers, manage a group of products (which exists in the product table) for a client (which exists in the client table)

First things first, you need this Key account managers table in the model,  and very importantly, without any relationship to any other table (as it probably shares field names with other tables watch out for unwanted relationships if you don’t have relationship detection turned off).

Now, you need a calculation group (yey! ) which naming isn’t easy, but I called it KAM relationships.  This will also show as table in PBID, and should be without any relationship to any other table. So, what about the calculation items?

Basically you’ll need just one to make this «faux  relationship» happen

Calc Item =
CALCULATE (
    SELECTEDMEASURE (),
    TREATAS ( VALUES ( 'KAM'[Group of Products] ), 'Product'[Group of Products] ),
    TREATAS (
        VALUES ( 'KAM'[Customer] ),
        'Customer'[Customer]
    )
)

I think you may be able to do that in a single TREATAS with two columns, but I felt better this way, passing only different values for both ‘group of products’ and ‘customer’.

You are supposed to add a neutral one which just returns «SELECTEDMEASURE()» to disable this calc group, but not sure if it applies in this case.

And that’s it. If this calc item is filtering your visual or page or whatever, it will be as if you have this double relationship active . It will not be as quick as real one-to-many relationships, but they’ll to their thing. You can add this post to the list «All the things you can solve with calculation groups»

Regards!