Forum Discussion
Measure of a Measure
- 7 years ago
Basically ALLEXCEPT doesnt do what i thought it did. I took out the ALLEXCEPT (both of them) statement and it worked perfectly.
I figured this problem out 90% of the way... however there is one part of it that is not working. Here is the final code that kinda works:
StarRating = SUMX(VALUES('Table'[ChildName]),CALCULATE(IF(SUM('Table'[Denominator])=0,0,MAX('Table'[Weight])*
IF((SUM('Table'[Numerator])/SUM('Table'[Denominator]))>=MAX('Table'[Star5]),5,IF((SUM('Table'[Numerator])/SUM('Table'[Denominator]))>=MAX('Table'[Star4]),4,IF((SUM('Table'[Numerator])/SUM('Table'[Denominator]))>=MAX('Table'[Star3]),3,IF((SUM('Table'[Numerator])/SUM('Table'[Denominator]))>=MAX('Table'[Star2]),2,1)))
)),ALLEXCEPT('Table','Table'[ChildName],'Table'[ParentName])))/SUMX(VALUES('Table'[ChildName]),CALCULATE(MAX('Table'[Weight]),ALLEXCEPT('Table','Table'[ParentName],'Table'[ChildName])))
I had to clean it up a little for presentation, so the parenthesis may not be pefect. Basically I took the calculation i was using to determing StarRating at the Child level (that is huge nested IF statement in the middle of the code).
I needed to group by the Parent and Child and then Sum the results. However, the entire report is filterable by HMO, RPPO, and/or LPPO. There has been no issue with the report abiding by this filter... however the above code seems to ignore this filter. Any idea what is going on?
Basically im asking if my suspicions are correct that ALLEXCEPT() overrides any filters (filters via the Slicer Visual i mean).
Basically ALLEXCEPT doesnt do what i thought it did. I took out the ALLEXCEPT (both of them) statement and it worked perfectly.