Forum Discussion
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:
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 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
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