locked
Number not reflective of slicer RRS feed

  • 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 12-6.

    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 Paul-NYS 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 12-6, 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 Paul-NYS Wednesday, January 27, 2016 6:31 PM
    Wednesday, January 27, 2016 6:31 PM