Solved: Reports based on a dynamically filtered slice (as ... - Page 2

Richard_Brouwer · 01-12-2021 03:08 PM

I have a dashboard view based on a dynamically filtered slice of my main table, which will display records based on calculations relating to several filter fields. Works perfectly for both single and multiple choices and combinations of each.

If I try to base a report off this filtered slice, a blank table is returned. This is the same behaviour as if I try to view the data of the filtered slice in the Data menu - it seems like the filters are only applied/calculated when the slice is ‘called’ in a view.

My question is how can I set up a filtered report based off a slice - I can get around it by making new slices with all the possible permutations and combinations of what the filter fields are but then I end up with 30+ slices and the same number of reports and templates to edit etc. and it gets very messy to manage.

Steve

Here’s your expression, reformatted to my taste:

AND(
  IF(
    ISBLANK(
      IN(
        [Hospital],
        Procedure Filter[fHospital]
      )
    ),
    TRUE,
    IN(
      [Hospital],
      Procedure Filter[fHospital]
    )
  ),
  IF(
    ISBLANK(
      IN(
        [_Year],
        Procedure Filter[fYear]
      )
    ),
    TRUE,
    IN(
      [_Year],
      Procedure Filter[fYear]
    )
  )
)

Note that these are nonsensical:

ISBLANK(
  IN(
    [Hospital],
    Procedure Filter[fHospital]
  )
),

and:

ISBLANK(
  IN(
    [_Year],
    Procedure Filter[fYear]
  )
),

The result of IN() will never be blank: it will be either TRUE or FALSE.

Typically, when one has a filter table, the goal is to treat an empty filter parameter as “match any”, but if it has a value, to match that value. An expression that should achieve that is:

AND(
  OR(
    ISBLANK(Procedure Filter[fHospital]),
    IN([Hospital], Procedure Filter[fHospital])
  ),
  OR(
    ISBLANK(Procedure Filter[fYear]),
    IN([_Year], Procedure Filter[fYear])
  )
)

Reports based on a dynamically filtered slice (as per Dashboard)