How to keep some filters with CALCULATETABLE or ignore some external filters?

[Anonymous]

I am building a report that will have a couple of filters applied to all pages. The filters will be the company and the year. Each page in the report will show related data about the selected company in the selected year. However, i have one scenario where I need to display a history for that company that will span for all years in the dataset. I need to generate a  table that retains the company filter, but excludes the year filter. How do I accomplish this?

The sample file:

report pbix 

Sample dataset:

Company and year selected on page 1:

What I want to show on page two (ignoring the year filter):

What I am getting instead:

using this expression:

[daxer-almighty]

More precisely, a page-level filter can't be overriden in the sense that data displayed in visuals as labels (attributes of dimensions) will always obey the filter. But in DAX measures you CAN override page- and report-level filters to do calculations on data that's not visible on the page.

[daxer-almighty]

I don't think it's possible. A filter for a page or a report is one that can't be overridden in DAX. The same is true for RLS. So, if you want to show different data on different pages, you have to use page-level filters, not report-level ones.

[Anonymous]

In the pbix i provided I demonstrated overriding the page filter in "table 3":

I assume you are saying that you know its possible to do it that way, but that it can't be done dynamically using the current filter context based on the page filter selections?

[v-kkf-msft]

Hi @Anonymous ,

I think you need to add a new table: 

Table2 = GROUPBY('Table','Table'[ID],'Table'[Year])

Then create relationship between Table[ID] and Table2[ID]:

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

@v-kkf-msft  This does not work for me. I couldn't open your workbook because I am using an earlier version of the desktop app.  But I can't create the relationship because there are non-unique values in both tables. 

[v-kkf-msft]

Hi @Anonymous ,

If you don't want to add other tables, you can try to synchronize slicers for the Year column instead of a report-level filter. Then change the interaction type for the visual to none.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.