How to use USERELATIONSHIP correctly

[PetyrBaelish]

I have been trying to use USERELATIONSHIP, but can't get it working in my example below. This is for a Power BI report with a live connection to a SSAS tabular model:

My data is as follows:

From the 3 tables above, there is a relationship between the Incident Facts table and Incident Details table on the Incident ID column.

There is an inactive relationship between the Dates table (DatesKey) and Incident Facts table (CompletedDateKey).

I have the following measures on the Incident Facts table:

RatingAverage:=CALCULATE(AVERAGE('Incident Details'[FeedbackRating]),filter('Incident Details','Incident Details'[RatingGiven]=TRUE),USERELATIONSHIP('Incident Facts'[CompletedDateKey],Dates[DateKey]))

RatingGivenCount:=CALCULATE(COUNTAX(FILTER('Incident Details','Incident Details'[RatingGiven]=TRUE),'Incident Details'[RatingGiven]),USERELATIONSHIP('Incident Facts'[CompletedDateKey],Dates[DateKey]))

RatingGivenPercent:=CALCULATE(IF('Incident Facts'[Completed]<>0,'Incident Facts'[RatingGivenCount]/'Incident Facts'[Completed]*100,""),USERELATIONSHIP('Incident Facts'[CompletedDateKey],Dates[DateKey]))

Completed:= CALCULATE(COUNTROWS('Incident Facts'),FILTER('Incident Facts','Incident Facts'[Completed]=1),USERELATIONSHIP('Incident Facts'[CompletedDateKey],'Dates'[DateKey]))

The Completed measure is working perfectly. For the others however, when I place them into a table visualisation with Year-Month and Date from the Dates table and the 4 measures above only the Completed measure is accurately breaking down per month/date. RatingAverage and RatingGivenCount are showing the same value for every entry and RatignGivenPercent is also inaccurate (I can see that it's dividing between a static RatingGivenCount and a dynamic Completed).

My question is, why aren't my RatingAverage, RatingGivenCount and RatingGivenPercent measures relating to my dates table?

Here an example of the ouput:

[v-lili6-msft]

hi, @PetyrBaelish 

For [Completed] and [RatingGivenPercent] works well but [RatingAverage] and [RatingGivenCount] doesn't.

So i think there should be something wrong on other side.

Could you please import some sample data and do a simple sample pbix file (just could reproduce the issue) for us have a test?

Best Regards,

Lin

[PetyrBaelish · Accepted solution]

Hi @v-lili6-msft,

I figured out the problem while preparing a test pbix file - it was my relationships (Incident Facts was on the many side of many to one relationships with both of the other tabels). Changing the Incident Details to Incident Facts relationship as a 1:1 relationship has solved my problem.

Thanks for your help.

[v-lili6-msft]

hi, @PetyrBaelish 

You may try to change conditional 'Incident Details'[RatingGiven]=TRUE to 'Incident Details'[RatingGiven]="Yes"

And then it will work well.

Best Regards,

Lin

[PetyrBaelish]

Hi Lin,

My original post was inaccurate. The RatingGiven column is actually a TRUE/FALSE column as per below:

=IF(ISBLANK('Incident Details'[FeedbackRating]),FALSE,TRUE)

I have tried using "Yes"/"No" and TRUE/FALSE but neither is producing the output I'm expecting (which is different figures for different months).