cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
riic0
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
MFelix
Super User
Super User

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
MFelix
Super User
Super User

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



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
Regular Visitor

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


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



riic0
Regular Visitor

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

 

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



riic0
Regular Visitor

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

 

Thanks again!

 

Regards,

riic0

Abduvali
Skilled Sharer
Skilled Sharer

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
Regular Visitor

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. 

 

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
Regular Visitor

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

@riic0,

 

Could please share the sample data?

 

It would be helpful to assist.

riic0
Regular Visitor

Hi @SivaMani,

 

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

 

Regards,

riic0

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors