Newbie Question re Date Table Relationships

[NikkiR]

I have set up a date table in my power pivot window and related it to one field in another power pivot table, named Opportunity.

I have tried to set up a second relationship from the date table to another field in the same Opportunity table, however because I have the first relationship set up, it won't activate the second relationship.

As a newbie to PowerPivot I'm not 100% how to work around this...

could someone please help...

thanks in advance.

 

[JasTatla]

hi - see this article - had the same issue last year .
http://www.mrexcel.com/forum/power-bi/679673-powerpivot-date-table-problem.html

 

[NikkiR]

Thanks for the link.

Although this will be useful for some of the reports I will be extracting from the data, in my report the date fields are used in slicers. I need to filter down to records from just one academic year. Having taken a look there are no measures on the slicers.

Would I be better setting up a VLookup?

 

[JasTatla]

you dont need to worry about the slicer as such.
steps i would suggest are: create the relationships bewtween the date keys you are interested in. i.e.

DateTable[DateKey] -> Opportunity[Date1]
DateTable[DateKey] -> Opportunity[Date2]

then you need to create 2 measures for each relationship

[Date1]: = COUNTA('Opportunity'[Date1])
[Date2]: = COUNTA('Opportunity'[Date2])

These should just give your counts for each situation. Now you can create 2 more measures based off of these measures to utilize the 2 relationships:

[Date1Cnt]:= CALCULATE([Date1], USERELATIONSHIP('DateTable'[DateKey],'Opportunity'[Date1]))
[Date2Cnt]:= CALCULATE([Date2], USERELATIONSHIP('DateTable'[DateKey],'Opportunity'[Date2]))

After you have completed them you then create your charts/Pivot and use the dates (month/days) from the date table and then use Date1Cnt/Date2Cnt as your Values. you will then be able to slice using your date slicer