Bridge Table? Surrogate Key? to fix a many-to-one one-to-many

[calcstuff]

I will paste my current data model below. I've got my Sales fact table that relates to my Customer dimensional table through the CustomerKey field in a many-to-one relationship. Some customers have several emails so I have to create another table named Email to keep the Customer table one row per customer. Then the relationship between Customer and Email tables is one-to-many. Now I can't filter the Sales table by Email without doing a bidirectional filter. What's the best practice for these type of situations? Bridge table? Surrogate key? I can't figure out how to do this the "best way". I can't find any YouTube videos that go over this type of situtation which occurs in almost all of my data sets.

[Anonymous · Accepted solution]

Hi,

Thanks for the solutions miTutorials  and girishthimmaiah  offered, and i want to offer some more information for user to refer to.

hello @calcstuff , you can use crossfilter() function, it can change the filter direction by using dax without change the model,  you can refer to the following sample measure.

Sample =
CALCULATE (
    SUM ( 'Sales'[Sales Amount] ),
    CROSSFILTER ( 'Customer'[CustomerKey], 'Email'[CustomerKey], BOTH )
)

And you can refer to the following link to know more about the function.

CROSSFILTER function - DAX | Microsoft Learn

Best Regards!

Yolo Zhu

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

[miTutorials]

Check out this Youtube video  - Hope this helps!

Power BI Many-to-Many Relationships: Issues and Fixes Explained | MiTutorials - YouTube

[girishthimmaiah]

A bridge table should be created with CustomerKey and Email. The Sales table is linked to the Customer table (many-to-one), while the Email table is linked to the bridge table (many-to-one). Then use DAX to apply filters from the Email table to the Sales table.

Total Sales by Email =
CALCULATE(
[Total Sales],
FILTER(
CustomerEmailBridge,
CustomerEmailBridge[Email] IN VALUES(Email[Email])
)
)

[calcstuff]

Isn't that what I already have? What you refer to as the bridge table is my current table named Email. This Email table isn't able to filter Sales. That's my whole problem and I think it's because my data model is wrong.