Forum Discussion

lbudack's avatar
lbudack
Icon for Advocate III rankAdvocate III
6 years ago
Solved

Date Dimension Model Relationship Woes

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.) 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

    • lbudack's avatar
      lbudack
      Icon for Advocate III rankAdvocate III
      6 years ago

      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's avatar
        Anonymous
        Not applicable
        6 years ago

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

         

        BR,

        DR

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      6 years ago

      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