Forum Discussion
Userelationship returns wrong results
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!
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
13 Replies
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
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
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
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
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.
