dealing with many to many relationships

[M-IMS]

How would you deal with this to make it star schema? many to many works for now but curious how others will handle this

problem: one email can exist under several lists, and several rows per email in revenue table for different dates and under different affiliate

I think the business should decide which list the revenue should be attributed to, but sadly they don't want to think about that, at least for now.

goal, have a matrix:

• list (from fact_coreg)
• phone (from fact coreg) - this might be deleted later, need it for now.
• affiliate_id (from fact_everflow)

then measures:

• count phones from fact_coreg
• sum revenue from fact_everflow

if I use the dims that I created, both lists would show the same revenue.

[v-sathmakuri]

Hi @M-IMS ,

May I ask if the provided solution helped in resolving the issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you!!

[v-sathmakuri]

Hi @M-IMS ,

Thank you for reaching out to Microsoft Fabric Community.

Create a bridge table as below

Bridge_EmailList =

ADDCOLUMNS(

    SUMMARIZE(fact_coreg, fact_coreg[email]),

    "AssignedList",

        VAR CurrentEmail = fact_coreg[email]

        VAR MinDate =

            CALCULATE(

                MIN(fact_coreg[purchase_date]),

                fact_coreg[email] = CurrentEmail

            )

        RETURN

            CALCULATE(

                VALUES(fact_coreg[list]),

                fact_coreg[email] = CurrentEmail,

                fact_coreg[purchase_date] = MinDate

            )

)

Then create the below measures, this should give the expected result. Please let us know if you have any further questions.

Revenue_OneListAttribution =

CALCULATE(

    SUM(fact_everflow[revenue]),

    TREATAS(

        VALUES(Bridge_EmailList[AssignedList]),

        fact_coreg[list]

    )

)

Count_Phones =

CALCULATE(

    DISTINCTCOUNT(fact_coreg[phone]),

    FILTER(

        fact_coreg,

        fact_coreg[list] IN VALUES(Bridge_EmailList[AssignedList])

    )

)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you!!

[M-IMS · Accepted solution]

I ended up using list|phone and list|email to go around this.

[ray_aramburo]

Hi @M-IMS , what's the difference between fact_everflow and fact_coreg and why are they related?

[M-IMS]

fact_everflow, this the revenue transactions, 100% fact table.

fact_coreg, not sure if it is actually a fact or a dim, but basically, it is the email, the list they exist under, date when they were purchased, then there will be other info about placement that the business haven't decided yet.

I think it is a business logic issue, but they don't want to decide on something yet.

for now the many to many works, obvios problem is that revenue for one phone number will be attributed to both lists, so outside of row context the total won't be right, but they're okay with that.

[ray_aramburo]

Yeah. Purchase date sounds like it should be in the everflow one. That way you only have a unique email list and makes sense for a dimension table. You can break it that way on the mean time.

[M-IMS]

purchase date of the sales lead (email)

date in everflow is the transaction date