Many to One relationship not working...Help!

Hi all,

In my data model, I have three tables specificallythe issues lies with, the Campaigns, Prospects and CampignEmailEvent tables.

The Campaigns table is the lookup table and the Prospects and CampaignEventEmail are fact tables with multiple values.

The relationship between the Campaigns table and the Prospects and CampaignEventEmail tables are many to one and setup correctly, see Image #1 but for some reason when I tested the model, it is showing repeated value, see Image #2

I went through the steps to make sure the data types are the same and did all the checks but for the life of me can’t figure out why the relationships are not being recognized.

What am I missing? Why can’t Campaign table not recognize CampaignEventEmail or Prospects table not recognize CampaignEventEmail table? I have also attached the WIP file.

Image #1

Image#2
image

WIP File.pbix (50.5 MB)

Hi @ysherriff,

Please review the filter direction in your model between these two tables.
You have an Attribute from the many side in your visual and performing the calc over the table on the one side, so its to be expected that no filtering is applied…

You could introduce a CROSSFILTER in your measure if you are unable to obtain that Attribute from the one side of the relationship.

HubSpot Campaigns v2 = 
 Calculate(
     COUNTA(Campaigns[HubSpot Campaign Name]),
      FILTER(Campaigns,
      Campaigns[HubSpot Campaign Name]<>""),
      CROSSFILTER( CampaignEmailEvent[Contact ID], 'Campaigns'[Contact ID], Both )
)

I hope this is helpful

1 Like

You are a genius and it works perfectly.

Can you send me a link in your resource where I can learn more about advanced data modeling techniques?

Thank you Melissa and thank you very very very much…

This response by @BrianJ contains enough resources to get started… I think
All the best.

Thank you very much Melissa.

1 Like