Ignore report level filter in a measure

1
votes

I've read numerous posts about this, it still doesn't work for me. My scenario is as below -

I have a Date dimension (DimDate) and a Fact (StockAvailability). There are several visuals on the page and a page level filter that shows data for the last 7 days. However, for one of the visuals, I want the data to be shown for last 3 months, i.e., not limited by 7 days report level filter.

So, I created a measure, clearing up the filter on the StockAvailability using ALL, but that results in a single value for all the dates -

Overall Stock Availability = CALCULATE(AVERAGE([Availability], ALL('StockAvailability'))

Further, I try to re-add the 3-months filter above, but still nothing - I get distinct values this time, but only 7 days worth

Overall Stock Availability =
    CALCULATE(AVERAGE([Availability]),
        ALL('StockAvailability'),
        FILTER(DimDate, [IsLast3Months] = true))

How can I Ignore the 7 days report level filter for this given measure?

Any inputs will be greatly appreciated!

Thanks

2
powerbidax

2 Answers

1
votes

It's an old question but in case it's still unresolved. I've just had a very similar issue.

I'm assuming you have a relationship between the StockAvailabilty table and DimDate based on a date column in StockAvailability.

For my issue, I put ALL(DimDate) and then apply a FILTER to ALL(StockAvailability[DateColumn])

Overall Stock Availability =
VAR Date3MonthsAgo = EDATE( TODAY(), -3)
RETURN
CALCULATE( 
  AVERAGE( [Availability] ),
  ALL( DimDate ),
  FILTER(
    ALL( StockAvailability[DateColumn] ),
    StockAvailability[DateColumn] >= Date3MonthsAgo
  )
)
0
votes

I'm guessing that this isn't working because your ALL function is removing the filtering on the StockAvailability table but not the DimDate table.

In your second measure above, the DimDate table is evaluated in the filter context it is being evaluated in, which means the last 7 days filter applies here. To ignore that filter, apply the ALL function to DimDate.

Overall Stock Availability =
    CALCULATE(AVERAGE([Availability]),
        ALL('StockAvailability'),
        FILTER(ALL(DimDate), [IsLast3Months] = true))

Note: I'm not positive you actually want the ALL('StockAvailability') part. Try it with and without.