Latest Enterprise DNA Initiatives


ALL not ignoring filter - example attached

Power BI
#1

Hi,

Again I seem to be struggling with ALL and it not ignoring the filter as I would expect.

My scenario (based on some mock up data) is that we have sales for 2019 Jan through to July, however we only have Targets from April onwards.

I have a tab on my report, Sales vs Targets, which details sales performance against targets and for most of the tables, cards etc we only want to compare sales for where we have targets. I therefore want to hard code this page to ignore all sales pre April 2019. However, the exception being that we do have a bar chart which shows sales by month and for this, we would like to show sales for the whole year - Jan to July.

I have created a column in my Dates table as such:

Is Prior to 1st April 2019 = IF(Dates[Date] < DATE(2019,4,1), TRUE(), FALSE())

I have added this column to my Filters On This Page and set it to FALSE, thus restricting all the data on the report to April 2019 on.

I have then created a special Sales measure for use in the bar chart:

Sales for Chart =
CALCULATE(SALES[Sales], ALL(Dates[Is Prior to 1st April 2019]))

However, this doesn’t work as I would expect and the chart is still restricted to April, May and June, when I want it to show Jan to July.

What am I missing here! Mock up example is attached:
Sales vs Target - ALL query.pbix (121.0 KB)

Many thanks!

Mark

#2

@Mark,

What’s giving you the problem here is the page-level filter you’ve set in the filter pane. If you delete that, and set your filters at the visual level rather than the page level, you’ll get the range you want on your bar chart.

image
image.png1303×612 35.5 KB

Full solution file posted below.

#3

Hi Brian,

Thanks for looking at this for me. Are page level filters treated differently to others then?

In reality, my actual dashboard has many more sales measures, for many different sales types, and many more visuals - cards, tables, charts etc. I was hoping to be able to code an exception for this one visual, not having to apply a filter to everything else.

Thanks

Mark

#4

Hi,

I have moved the filter from being a Page Level filter, to simply being a normal slicer, yet my bar chart is still reacting to it, when I select True or False?

I would like to understand why this is. My expectation is that the measure ‘Sales for Chart’ would ignore this and show all months?

Please see Page 2 on v2: Sales vs Target - ALL query v2.pbix (123.8 KB)

Thanks

Mark

#5

Mark,

One option you can look at is to not have the “Is Prior to 1st April” slicer connected to the Sales chart. If you click on the slicer and then go under the “Format” on the ribbon bar, there is an option to “Edit Interactions”. If you select this, then select your slicer, and then on the top right of the bar chart - you should see a circle. This toggles between whether the slicer interacts with this chart or not.

Then - if you select the “False” item in the slicer - the bar chart won’t change and will still show all sales.

(Note - in your bar chart - I changed the Value column to just reflect to the Sales measure that you had set up).

Not sure if this is what you are looking for, but I wanted to mention.

Thanks,

Tim

#6

Hi Tim,

Good point. I had forgotten about this option. I have now hidden my slicer from view, set it to False so all visuals report post April, but for the Sales Chart I have set the Interaction option to NONE. Nice and simple with minimal changes required.

However - I would still really like to know why my Sales For Chart measure using ALL() is not ignoring my filter?

Thanks

Mark

#7

Generally ALL() doesn’t ignore filters. However, ALLEXCEPT() can be used to specify those filters to not ignore. Perhaps try that?

#8

Clarification. ALL() ignores filters. ALLEXCEPT() ignores filters except those that you specify.

#9

Hmm. this is either a good question or because it just Friday. But it should work pretty straight-forward, but not so much. Changing the interaction should suffice, but will look into more. Definitely a reason, probably has something to do with the filter context and such. Might have to build a virtual table or two, but that’s just a guess