Data Modelling, filter propagation, use of bridge table?

[_GianmaBI46]

I have a powerbi model with 4 tables, Agent, Partner, Agent_brand, Sale order. The table Agent and Partner contains unique values of agent_id and partner_id. I created the table Agent_brand which has duplicate values of partner_id because a partner_id can have multiple agent_id depending on the column brand_id and duplicate values of agent_id beacuse the same partner_id can have the same agent for the two brand_id. I have established a bi-directional relationship among agent_brands, Agent and Partner. Agent_brand being the many side in both cases. Then the table partner filter the sale order table to get sales data. When I put in a table the agent_id from agent, partner_id from partner and Net Sales which is simply Sum(Sale order[price]) I get wrong totals. Meaning that an agent has the two cells correspondig to the brands blank and the total incorrect.

Total is incorrect and client 66545 should not be in the matrix under the agent_id 50406.

It is logic to me that withouth filtering for a specific brand the filter propagation do not understand to which agent assign the sales but how can I show the cumulative view? Right now the sales can be shown if there's a filter on brand. I need to have single values of agent_id and partner_id to be able to filter.

Thanks,

[Anonymous · Accepted solution]

Hi @_GianmaBI46 

Thank you very much Ritaf1983 for your prompt reply.

Pls try this:

"Agent"

"Partner"

"Agent_brand"

"Sale Order"

Create a measure.

Net Sales = SUMX(
    FILTER(
        'Sale Order', 
        'Sale Order'[agent_id] IN VALUES('Agent'[agent_id]) &&
        'Sale Order'[partner_id] IN VALUES('Partner'[partner_id])
    ),
    'Sale Order'[price]
)

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ritaf1983]

Hi @_GianmaBI46 

Please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly