cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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.

Knipsel.PNG

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:

Knipsel2.PNG

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

Knipsel3.PNG

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Userelationship returns wrong results

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:

raqw data.png

 

Option 1) active relationship on open dates:

atyive.png

 

Option 2) inactive relationship

inac.png

 

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

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

13 REPLIES 13
Highlighted
Skilled Sharer
Skilled Sharer

Re: Userelationship returns wrong results

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

Highlighted
Regular Visitor

Re: Userelationship returns wrong results

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. 

 

Highlighted
Solution Sage
Solution Sage

Re: Userelationship returns wrong results

@riic0,

 

Could please share the sample data?

 

It would be helpful to assist.

Highlighted
Super User III
Super User III

Re: Userelationship returns wrong results

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:

raqw data.png

 

Option 1) active relationship on open dates:

atyive.png

 

Option 2) inactive relationship

inac.png

 

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

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

Highlighted
Skilled Sharer
Skilled Sharer

Re: Userelationship returns wrong results

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

Highlighted
Regular Visitor

Re: Userelationship returns wrong results

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

Highlighted
Super User III
Super User III

Re: Userelationship returns wrong results

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Regular Visitor

Re: Userelationship returns wrong results

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

 

Highlighted
Super User III
Super User III

Re: Userelationship returns wrong results

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors