Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
Solved! Go to 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.
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
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.
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
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).
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |