Filter Slicer content based on existance of measures


I want to create a slide in Pyramid which shows a table with a measure sales of the 12 last months. 

The measure Sales is connected to a dimension hierachy (Organisation: subteam, team, label) which i use to populate the report's slicer. The problem is, that the slicer contains all members, so also members which don't have Sales in the last 12 months. This kind of contiminates the slicer because it shows all kinds of non-relevant subteams, teams.

My question: is there a way in pyramid to filter slicer/dimension hierarchies based on the availablity of measures in a certain period? Like in the Formulate-pane?

Kind regards,

Rutger Lammers.

2 replies

    • Yakov_Shaul
    • 4 yrs ago
    • Reported - view

    Hi Rutger Lammers ,
    If I understood right, It seems you're almost there.
    If you've managed to filter your set in the Formulate, just save it and use it in the slicer selection of your report.

    Example of a custom set, To get the last 12 months that have sales values:

    tail(nonempty(allmembers([dates].[months]), [measures].[sales)), 12)

    Kind Regards,

      • Ray_Johnson
      • 4 yrs ago
      • Reported - view

      Yakov Shaul this wouldn't work for me but tail(nonempty([dates].[months].allmembers, [measures].[sales]),12) seems to (not sure if I'm missing something though) 

Content aside

  • Status Answered
  • 4 yrs agoLast active
  • 2Replies
  • 33Views
  • 3 Following