Log In
Register
Home
January 28, 2021
Offline
Hi,
I have imported data into PowerPivot via Power Query into the data model. I have created a calendar table, with bank holidays in order for me to calculate working days etc. The data relates to a query management, and includes columns 'date opened' and 'date resolved'.
I want it to result in a simple summary pivot table which shows : date , count of date opened & count of date resolved. I.e. how many new queries were opened in the month, how many were resolved.
I have created the relationship from the calendar table to 'date opened', but as you cannot have multiple active relationships, I cannot seem to get an accurate measure of the queries resolved in the same time period. I have seen what looks like a PBI only answer, where you can use a measure to provide the result, but can't make it work in my data model. The other option appears to be a duplicate calendar table, which is less preferred but would provide the answer I need - but I can't find a way to duplicate a whole table in PP (my calendar table was created in PP).
Am I missing a simple answer here? Any help would be appreciated. The data feed an excel dashboard (no company license for PBI yet) so I want it to refresh and update all the pivots without the need for any manual work each period.
Thanks in advance
July 16, 2010
Offline
Hi John,
When you have an inactive relationship you can write measures that activate the relationship using the 'USERELATIONSHIP' function. If you're still stuck, please provide a small sample file that illustrates the issue so we can help you further.
Mynda
January 28, 2021
Offline
Hi Mynda,
Thanks for your reply, I'm not sure where I would include that function.
I've remade a crude version of the file and attached. In order for the dates to build the relationship I've truncated them, and you can see on the pivot sheet how the calculations are showing at the moment, as well as how I would expect them. Appreciate your help. Thanks
John
January 28, 2021
Offline
Hi Mynda, I've managed to resolve this, in the end I created a column to add an arbitrary value if resolved, and the used the USERELATIONSHIP with a SUM inside a calculated.
Thanks for your help.
Kind Regards
The following users say thank you to John Murphy for this useful post:
Mynda Treacy
1 Guest(s)
