Solved: Userelationship returns wrong results - Page 2

riic0 · ‎11-14-2017

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 · ‎11-14-2017

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

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