cancel
Showing results for
Did you mean:
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.

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?

1 ACCEPTED SOLUTION
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:

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

13 REPLIES 13
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:

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

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Skilled Sharer

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

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.

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

Super User

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

Proud to be a Super User!

Check out my blog: Power BI em Português

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

Super User

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.

Regards,

MFelix

Regards

Miguel Félix

Proud to be a Super User!

Check out my blog: Power BI em Português

Regular Visitor

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

Thanks again!

Regards,

riic0

Skilled Sharer

Hi @riic0,

• 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

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.

Skilled Sharer

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

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

Resident Rockstar

Could please share the sample data?

It would be helpful to assist.

Regular Visitor

Hi @SivaMani,

Regards,

riic0

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors