Powerpivot Date table problem

[Ormondo_99]

Hi,

I'm really stuck on something and need some help urgently please. I have something to present to my CEO this week and I cant get my date table to work to compare data dates.

In short I have my power pivot hooked up to a SQL Stored procedure which extracts a number of fields however there are two fields I need to compare are
1. Logged Date
2. Resolved Date

The data I'm looking at are service desk incident data.


I need to have this on one single line chart chart to compare the number logged and the number resolved each month.

The problem I'm having is I can only connect one relationship to my date table at a time so my figures are out for the other. If I connect logged date colum to the Date Key column in the date table then I get the correct figures for logged. But if I also try and connect the Resolved date column the date key column in the date table then the relationship goes to "active No".

I'm using the the Azure date stream table for my date table.

What am i doing wrong as I beleive this should be pretty simple.

 

[MD610]

You must first create relationships between the date table and both date fields in your data.

In the measures you use to SUM your incidents you must define which relationship to make active. You do this by using USERELATIONSHIP() as a filter parameter in a CALCULATE().

So you will have one measure that sums the logged incidents using the Logged Date relationship and a second measure the sums the resolved incidents using the Resolved Date relationship.

 

[Ormondo_99]

Quote:
"You must first create relationships between the date table and both date fields in your data."

So create the relationship even though one with not be active at first in the "Manage relationship" console?

 

[MD610]

Yep. The USERELATIONSHIP() function will override that when calculating the measure.

 

[Ormondo_99]

Ok,

How\where do I do that?

 

[MD610]

In your measures. For example, I assume your data table contains all Logged incidents and then a column indicating whether or not the incident was resolved.

So you should have these first 2 base measures (or something similar):

[LoggedIncidents]: = COUNTROWS('DataTable')
[ResolvedIncidents]: = CALCULATE([LoggedIncidents], 'DataTable'[ResolvedStatus]="TRUE")

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:

[LoggedIncidentDateCnt]:= CALCULATE([LoggedIncidents], USERELATIONSHIP('Date Table'[DateKey], 'DataTable'[Logged Date]))
[ResolvedIncidentDateCnt]:= CALCULATE([ResolvedIncidents], USERELATIONSHIP('Date Table'[DateKey], 'DataTable'[Resolved Date]))

Depending how your data is actually set up there are many different variations of the measures you could use. You could even just do it all in a single measure for each type but it is easier to build simple measures and then build upon them by using them in more complex measures.

Anyway you do it, this is the basic logic pattern to follow. These last 2 measures are what you would use to build your chart.

 

[Ormondo_99]

Hi,

Thanks for your reply but still having trouble.

The two columns in my table which is called "Phoenix" are:

[TABLE="width: 341"]
<tbody>[TR]
[TD]Logged
[/TD]
[TD]Resolved Date
[/TD]
[/TR]
[TR]
[TD]03/08/2012 00:00
[/TD]
[TD]06/08/2012 00:00
[/TD]
[/TR]
[TR]
[TD]08/08/2012 00:00
[/TD]
[TD]14/08/2012 00:00
[/TD]
[/TR]
[TR]
[TD]20/08/2012 00:00
[/TD]
[TD]22/08/2012 00:00
[/TD]
[/TR]
</tbody>[/TABLE]

There are other columns but with different data.

My date table is called “basiccalenderEnglish”

I have the below relationship in place:

Phoenix [Logged] to BasicCalendarEnglish [DateKey]

If I count I get error each time.

 

[MD610]

Since your first post said you can get the correct values for logged, I will assume you have the date relationship to 'Phoenix'[Logged] set up correctly. You now need to create a second relationship between 'Phoenix'[Resolved] and 'BasicCalendarEnglish'[DateKey]. It will say it is inactive but don't worry about that.

Are you just counting the date entries to arrive at each value? If so, then the measures below should work if you have set up the 2 relationships:

[LoggedIncidents]: = COUNTA('Phoenix'[Logged])
[ResolvedIncidents]: = COUNTA('Phoenix'[Resolved])

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:

[LoggedIncidentDateCnt]:= CALCULATE([LoggedIncidents], USERELATIONSHIP('BasicCalendarEnglish'[DateKey],'Phoenix'[Logged]))
[ResolvedIncidentDateCnt]:= CALCULATE([ResolvedIncidents], USERELATIONSHIP('BasicCalendarEnglish'[DateKey],'Phoenix'[Resolved]))

To set up your chart, use date fields (month, year, whatever...) from 'BasicCalendarEnglish' and then the second 2 measures as your value fields.

If this doesn't work and you still get an error, please post the exact wording of the error you get.

 

[Ormondo_99]

hi again,

I get this for both:

The value for column 'ResolvedIncidents' in table 'Phoenix' cannot be determined in the current context. Check that all columns referenced in the calculation expression exist, and that there are no circular dependencies. This can also occur when the formula for a measure refers directly to a column without performing any aggregation--such as sum, average, or count--on that column. The column does not have a single value; it has many values, one for each row of the table, and no row has been specified.

Formula for resolved is:
= CALCULATE(Phoenix[ResolvedIncidents], USERELATIONSHIP('BasicCalendarEnglish'[DateKey],'Phoenix'[ResolvedDate]))


does the Resolved Incidents need to be a seperate table?

 

[MD610]

[LoggedIncidents] and [ResolvedIncidents] are not columns. They should be new measures that you have created. So you should have 4 new measures even though only the last 2 will be used in your charts. Make sure you are creating measures and not just creating calculated columns.

Since [LoggedIncidents] and [ResolvedIncidents] are measures, when you reference them in the second set of measures you do not need include the table name so the formula should be:
= CALCULATE([ResolvedIncidents], USERELATIONSHIP('BasicCalendarEnglish'[DateKey],'Phoenix'[Resolved]))

Not

= CALCULATE(Phoenix[ResolvedIncidents], USERELATIONSHIP('BasicCalendarEnglish'[DateKey],'Phoenix'[ResolvedDate]))

No need for 'Phoenix' in the first arguement.