3 Replies Latest reply on Oct 16, 2012 12:35 PM by Mark Holtz

    Action Not Filtering Properly

    Cole Pacak



      Within a dashboard consisting of a bar graph and a list of students by test score, I created an action so that clicking on a chart element would filter the list. In the past this has been ridiculously easy, in that I create an action for the dashboard, select "Run Action on Select", and leave both sheets selected as Source and Target Sheets.


      So I was really surprised when the action didn't work this time. The dimensions are filtered, but the measure isn't.


      I'm working with academic assessment results and the bar graph charts the % of students who scored an 80 or higher on a given assessment. Like I said, the action filters the list's dimensions, but the list displays students with ALL scores, rather than just those with scores at 80 or above.


      Any thoughts?





        • 1. Re: Action Not Filtering Properly
          Mark Holtz

          Hi Cole,


          Your [% Above 80] measure is an aggregation based on the [Above 80] field.

          This [Above 80] field is calculated for every record in the data set.  You are returning 1 for values above 80 and 0 for values not above 80. [% Above 80] takes the sum(Above 80) / count(Above 80).


          Every record gets a 1 for this count field.

          Thus, every score is truly "part" of the underlying data in this aggregation. a score 0f 70 contributes a 0 / 1 to the aggregate, and a score of 90 contributes a 1 / 1...


          One way you could get around this is to create a parameter that lets the user choose what score threshhold to view.  You could base your [Above X] and [% Above X] measures on the parameter entered.  Then, you could filter the second view such that [Above X] must = 1.


          Hope this helps.

          • 2. Re: Action Not Filtering Properly
            Cole Pacak

            Thanks for your response, Mark.


            So it's the COUNT function that's getting me in trouble?


            Would it make a difference if the [Above 80] field said:

            IF [Score] >= 80

            THEN 1



            and skip the "ELSE 0"?


            If not, does it seem like I could get around this with a table calculation?


            Thanks for your help!



            • 3. Re: Action Not Filtering Properly
              Mark Holtz

              It's not so much what it's returning, i.e., 0 or NULL, it's that your [Above 80] field does not act as a filter by virtue of simply existing. You'd have to somehow choose "only the 1's" to in fact filter the second view. 


              I'm not sure there's a "hidden" way to do this, since you're representing the sum of the result of your IF statement all at once (thus blending 0's/NULL's and 1's and losing the ability to split between them)--in fact, you certainly would NOT want to filter to show only the 1's on the primary tab.  If that were the case, you'd see 100% all the time, because it'd ONLY show those who score 80% or higher...


              My suggestion of the parameter to select the 80% would also make it possible to make the measure on second tab conditional...


              I went ahead and attached the workbook showing what I'm describing.

              Note the new field shown with the filters--the parameter called "Show Scores Above."