Creating a Button to Filter Negative and Positive Values

This is a simple example of how to use a Variable and a Set to create a report with the ability to filter positive and negative values.

1.  Creating the variable:

        a.  In Data Discovery, go to the Extensions tab > Buttons.


        b. Give it a name and add two values to the button. One for Negative and one for Positive.


        c. Create a set that uses the variable button. I’ve used the following MDX code for the set:

when [Variable].[!##Negative / Positive##!] = 0
then {{FILTER({[Customer].[Custom Geography Zip].Customer].MEMBERS}, ([Measures].[Profit]) <0)}}
when [Variable].[!##Negative / Positive##!] >= 0
then {{FILTER({[Customer].[Custom Geography Zip].[Customer].MEMBERS},([Measures].[Profit]) >= 0)}}
else null


2.  Use the set in the report and run the query

3.  Optional: since the set code uses “MEMBERS”, you might want to limit the number of members that will in the report. You can do this by creating a Top Count filter in the Query tab > Filter.

The final result should look something like this:

Reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like Follow
  • 5 yrs agoLast active
  • 566Views
  • 1 Following