Userelationship returns wrong results

[riic0]

Hi all,

I have a table with incidents. All these incidents have a date on which they were openend and a date on which they were closed.

I want to show in one graph how many incidents were opened and closed every month.

I made two relations from my incidents table to my date table, one for the opening date and one for the closing date.

Gesloten - Date

Geopend - Date

When I make the "Gesloten - Date" relationship innactive and use the following measure:

CALCULATE (
 COUNT ('Servicenow Incidenten'[Nummer] ); 
 USERELATIONSHIP('Servicenow Incidenten'[Gesloten];'Date'[Date]);
 FILTER ('Servicenow Incidenten'; SEARCH("COMMUNICATIE";'Servicenow Incidenten'[Business service];;0) 
 )
)

Then it will return this in an graph:

But when I make the relationship active and remove the USERELATIONSHIP function, it will return the following:

These are the correct results.

Same happends when is do it on the "Geopend - Date" relationship.

Does anybody know why this happends? 

Thanks in advance!

[MFelix · Accepted solution]

Hi @riic0,

You need to have two measures one for open incidents and another for closed incidents in terms of relationships you need to have one of two options:

1) Linked the table by one active relationship and another inactive

2) Linked both table by inactive relationship

In option 1) you need to have one measure that make the normal calculation based on date and another one with the user relationship

In option2) both measure should be calculated based on userrelationship

The formulas should be something like this:

Close Cases = CALCULATE(COUNT(Table1[S]), USERELATIONSHIP('Calendar'[Date],Table1[End date]))

In the images below you have the image that show equal versions with and withou active relationship.

As you can sse in the second image when I make the inactive connection the measure having the userrelationship gives correct results the one for active relationships returns 6 for all columns:

Rawa Data:

Option 1) active relationship on open dates:

Option 2) inactive relationship

My measure are simply ones but you can add the filters and all the other complexity and should work.

Regards,

MFelix

[Abduvali]

Hi @MFelix,

I tried to replicate what you did but still not able to make it work =o? Can you share your pbix file please.

Thanks.

Regards

Abduvali

[riic0]

Hi @MFelix,

I was already using your option 1, sorry I didn't clarify that.

However, I used your option 2 this time and it worked! It returned the correct results.

Thanks for your help!

I only don't understand why option 1 didn't work for me, it should return the same results as shown in your picture.

Regards,

riic0

[MFelix]

You need to use Calendar Date in the X-axis for both options don't use one of the date columns on your main table because it will filter out the records based on that date and give you incorrect results no matter what type of measure you are using.

When you do it with the active relationship what date do you put on X-axis?

Regards,

MFelix

[riic0]

I used the Calendar Date on the X-axis for both options. Only with when one of the relationships is active(option 1) it returns incorrect results.

Regards,

riic0

[MFelix]

I believe this is a question related with the way the model mau be setup, but without further information cannot pin point the error you add, but since it's solved problably no need to check it.

Glad I could help you.

Regards,

MFelix

[riic0]

That could be the case. I'm allready happy it is working now.

Thanks again!

Regards,

riic0

[Abduvali]

Hi @riic0,

Can you please clarify why do you need Dates table when you already have a Date in your Servicenow Incidents table?

• If you want to display the number of Open and Closed then just drop your open date into column chart into the values and set it to COUNT
• Then create new CALCULATE column for ClosedIncidets or just drop your close date and set it to COUNT as well
  • ClosedIncidets = calculate(count(Status),Servicenow Incidents[Status]="closed") 

As each date stamp represents a ticket you can count dates instead.

Regards

Abduvali

[riic0]

Hi @Abduvali,

I want to count the closed and opened incidents for each month and show it in one graph so I can compare them.

If I use an column chart for example, I will put the month from the the date table on the Axis. That way it shows the results for each month. If I use the date from the incidents table then I have to choose between the opening date and the closing date. Which both returns wrong results, because for example when I use the Open date it counts the closed incidents on the date from when they were opened. 

I hope this makes sense.

Maybe what i'm trying to do is wrong. 

[Abduvali]

Hi @riic0, @SivaMani, @MFelix

I got your point and what you are trying to achieve but just for my own reference, please evaluate the following scenario:

1. You had 100 opened incidents in October (Open date Column)
2. 90 of those were Closed in October and 10 in November (Close Date column)
3. So if you drop those as values in a column chart and look at October it will display 100 open and 90 closed
   1. and the other 10 tickets will show up in November under Closed tickets

So I'm just trying to understand what will change if you use Date table???

• What do you expect to see from above example using Dates table???

Regards

Abduvali

[riic0]

Hi @Abduvali,

Like I dicussed with @MFelix, I need to put the month from the the Date table in order to get the results I want.

For a column chart I need to put the date on the X-axis. The dates from whe an incident is opened and closed are different, so I cant use those.

Maybe i'm not understanding you correctly.

Regards,

riic0

[SivaMani]

@riic0,

Could please share the sample data?

It would be helpful to assist.

[riic0]

Hi @SivaMani,

Thanks for your reply, but it's allready solved!

Regards,

riic0