Measure ignoring specific filter doesn't work when not using date hierarchy

[psergio]

Hi everyone,

I have a quite weird problem when using a measure that should ignore a filter. The measure is intended to show the percentage of completion over the total population for different points in time, in a line graph. I have as well another bar graph which shows more detail on completion but it is prefiltered to show only the status as of the latest date, so whenever one of the bar group is selected the line graph gets filtered on the latest date only, while I want to keep showing the whole set of dates in my line graph. I achieved this by creating the following measure:

Entered FWA % = CALCULATE(SUM('Consolidated'[Response]) / COUNTA('Consolidated'[GPN]), ALL(Consolidated[Reporting Date]))

The graph works fine when I have it configured to use Reporting Date with its Date Hierarchy, however rather than showing months I would like to show specific dates in the X axis (as a new report comes every 15-20 days). However, when I unselect the Date Hirearchy to use Reporting Date as it is instead, I get a flat line with the overall percentage of completion on every single data point.

 I am really not sure why does this happen, as regardless of the way of displaying it the table column excluded from the filter should be the same one.

Any help will be much appreciated. Thanks a lot in advance

[Greg_Deckler · Accepted solution]

@psergio So if you don't want the bar graph to affect the line graph, just use the Format tab in the ribbon and turn off interactions between the two using Edit Interactions. Then you wouldn't need the ALL filter clause or CALCULATE, just the division portion of your formula. If you think about it, the ALL clause the way you have it is replacing any filters on your Reporting Date column so if you use Reporting Date as the x-axis in your graph then it gets replaced by the ALL and hence you get the same number for all dates.

[psergio]

@Greg_Deckler thanks for your answer, I wanted to use the groups in the bar chart groups as a way to filter the line chart (to show the percentage of completion for particular groups) but I will probably add it as a slicer then. I am still curious why it works when I set it as a hierarch, but won't when I select particular dates.

Many thanks for the help

[Greg_Deckler]

@psergio If you want the interactions, then you should be able to just do this if you want just that group for the dates in context for that group:

Measure = DIVIDE(SUM('Consolidated'[Response]), COUNTA('Consolidated'[GPN]))

Again, the reason it works for Months and not [Reporting Date] is because your filter clause in your CALCULATE statement is ALL('Consolidated'[Reporting Date]). This replaces any filter context on [Reporting Date]. So, if [Reporting Date] is in your x-axis then the x-axis is filtering to a single date value. However, that context gets replaced by ALL dates and thus a single flat line. This does not happen if you use Month in your x-axis because the Month filter is preserved and ALL gets all dates in context of that month filter.

All of this nonsense can be avoided by just not using CALCULATE. https://youtu.be/meh3OkgFYfc