Date Dimension Model Relationship Woes

[lbudack]

I'm still learning proper data modeling, and I suspect one or more of these issues is true of my problem: 

1. I need another dimension table to parse out some values but not sure which ones.
2. I'm missing a needed relationship somewhere.
3. My existing relationship settings could be incorrect. 

Attached is a screenshot of my existing model. I'm having trouble with the dates. Do I need to link the date dimension to all existing dates in both of the other tables? I'm not getting the expected results when I filter by any date. I've read every previous date and data modeling post I could find, and I'm likely just more confused than when I started. 

Currently, the MktOpportunity field OpportunityNumber is linked to the OpportunityNumber in the MktCalendar table. 

The Date Dimension FullDateAlternateKey is linked to the StartDate in the MktCalendar table. (Not sure if this is correct.) 

[Anonymous · Accepted solution]

You have to do one of the two:

- Either you link the date to the MktOpportunity instead of linking it to MktCalendar; or

- You apply to the relationship between MktOpportunity and MktCalendar the both way relatioship and should work fine.

Hope it fixes it, let me know.

Best Regards,

DR

[amitchandak]

Maketing calendar to Marketing Opportunity should have been one to many (ideally).

Now if it is not. Then the date can join with both tables.

What is the calculation you want to achieve that is creating the problem.

Can you please explain with an example. If possible please share a sample pbix file after removing sensitive information.
Thanks

[lbudack]

Thanks @Anonymous 

I changed the relationship between MktOpportunity and MktCalendar to bi-directional as you suggested. I'm still not getting the expected results when I filter/slice by any of the dates in either table. It always seems to filter correctly on just what is in that table. Oddly, it's still pulling results from both tables, but the number of results is not correct. 

Is there anything else I should check for? 

[Anonymous]

Are you using the date table field to slice or a date from any of the tables?

BR,

DR

[lbudack]

@Anonymous 

I've been using a date from any of the tables. That's probably my issue since I have done this successfully before, but the multiple dates available are throwing me off. If I use one from the date dimension table, wouldn't I need to link it to the other date fields? 

[Anonymous]

Assuming you have a date for the MktOportunity (when it was created I assume) and with the bidirectional relationship pointing to the same MktOpportunity but when its released (MktCalendar).

So if you filter by the date field in opportunities it will show the mkt opportunities created in that range not the releases.

If you wanna filter by both dates you should turn that relationship into one-sided and create two date tables, one for the marketing oppportunity date and one for MktCalendar.

Can you explain what's the goal so it can be easier to help you?

BR,

DR

[lbudack]

It's starting to make a little more sense now. My goal is to be able to filter both tables together by a common date, such as the CreateDate, to compare the two counts. (Sample visualization attached. Activites come from the MktCalendar table, and the Outcome comes from the MktOpportunity table. The columns change if I pick a create date filter from either table which is why I can tell it's not working quite right.) 

[Anonymous]

In theory what you're filtering are all mkt opportunities created in a certain date and it filters both because of the bi directional relationship, so it doesnt happen you can turn the relationship onesided but then it will only filter (if you use the date table created) by the table you have date table connected to.

I don't think there's anything wrong with you're model, it's a matter to know the context in which you need to see the data.

BR,

DR

[lbudack]

Thank you so much for all your help! I think I understand relationships a lot better now. I'll keep testing some things, and I think I'll get to where I need to be. Thanks again!