Answered by:
Number not reflective of slicer
Question

Hi Everyone
I have a table below where I am calculating a number (#) across time intervals (6, 12, 18): this variable is equal to [PermExitCategory]. I want the number to the count of records that equal to 6 minus the time interval along the top of the table. So for 12, the number would reflect all records that have a [PermExitCategory] of greater than 126.
I have tried two different formulas below that work, but do not calculate the correct number. They seem to not correctly factor in slicer values. Is there a way to modify these to include slicer selections?
=calculate(COUNTROWS(analyticJudConsist),FILTER(ALL(analyticJudConsist[PermExitMonthCategory]),analyticJudConsist[PermExitMonthCategory] >= (MAX(analyticJudConsist[PermExitMonthCategory])6)))
=COUNTROWS (FILTER (CALCULATETABLE (analyticJudConsist,ALL(analyticJudConsist[PermExitMonthCategory])),analyticJudConsist[PermExitMonthCategory] > MAX (analyticJudConsist[PermExitMonthCategory] )  6))
Wednesday, January 27, 2016 12:35 PM
Answers

Actually, I solved my own problem for a change. I was just missing the MAX statement in the && statement above:
=calculate(COUNTROWS(analyticJudConsist),FILTER(ALL(analyticJudConsist[PermExitMonthCategory]),analyticJudConsist[PermExitMonthCategory] > (MAX(analyticJudConsist[PermExitMonthCategory])6) && analyticJudConsist[PermExitMonthCategory] <= MAX(analyticJudConsist[PermExitMonthCategory])))
Sorry to post this.
 Marked as answer by PaulNYS Wednesday, January 27, 2016 6:31 PM
Wednesday, January 27, 2016 6:31 PM
All replies

Slicers on what fields? What behavior should we see when a slicer selction is made?
Check out the Power BI User Group of Philadelphia.
Our next meeting is February 2 in Malvern, PA.Wednesday, January 27, 2016 5:39 PM 
Actually, it is not a slicer. The problem is with the inequality statement. The above statement says to count all [PermExitMonthCategory] values that are greater than [PermExitMonthCategory]  6.
That is fine for the minimum, however, it keeps counting beyond each [PermExitMonthCategory] value in the above table. So for 12, everything greater than 126, but it needs to stop at 12 (or whatever each PermExitMonthCategory value is in the above table).
What I am trying to do now is to add a ceiling equal to each PermExitMonthCategory value on pivot table using an And statement. However, this is not working:
=calculate(COUNTROWS(analyticJudConsist),FILTER(ALL(analyticJudConsist[PermExitMonthCategory]),analyticJudConsist[PermExitMonthCategory] > (MAX(analyticJudConsist[PermExitMonthCategory])6) && analyticJudConsist[PermExitMonthCategory] <= analyticJudConsist[PermExitMonthCategory]))
Paul
Wednesday, January 27, 2016 6:09 PM 
Actually, I solved my own problem for a change. I was just missing the MAX statement in the && statement above:
=calculate(COUNTROWS(analyticJudConsist),FILTER(ALL(analyticJudConsist[PermExitMonthCategory]),analyticJudConsist[PermExitMonthCategory] > (MAX(analyticJudConsist[PermExitMonthCategory])6) && analyticJudConsist[PermExitMonthCategory] <= MAX(analyticJudConsist[PermExitMonthCategory])))
Sorry to post this.
 Marked as answer by PaulNYS Wednesday, January 27, 2016 6:31 PM
Wednesday, January 27, 2016 6:31 PM