How to ignore external filters for specific columns.

[Anonymous]

I have a reference database, with several columns which are unrelated to one another. Although I should really have split these into different tables, the thought of having 40+ tables just for referencing is unappealing.

So, my issue is that I am referencing 10 of these columns, and have external filters for all of these.

When I am trying to calculate a measure based on one reference column, I want it to ignore the filter on the other 9 columns, but to include the external filter on the column I am referencing.

Since I intend to use the measures in the same visual, I can't just edit the interactions.

I would have thought that ALLEXPECT would have helped, as its description mentions that it ignores other column filters, but you have to then specify the filters you want within the function, which means it ignores the external filter.

Is there a way I can structure my formula so that I can ignore all external filters except for one, or do I have to split the columns into separate tables (or the measures into different tables)?

Example to help demonstrate:

Ref Table:

Table

Report Page:

[Anonymous]

If I have two slicers (or more) on a page affecting different columns on my base table, and wish to create a measure that ignores only one of the filters, how do I go about this?

Editing interactions is not viable for what I'm doing.

Currently I thought it would go like this:

If the values in the two columns range from 0 to 100 say, and I have these two slicers on the page:Two Slicers affecting Colums 1 and 2 respectively

I would want my function Max Variable 1 = 15, and Max Variable 2 = 30.

I would have thought I would work it out like below:

Max Variable 1 = CALCULATE(MAX(Table1[Column 1]),ALLEXCEPT(Table1[Column 1]))

Max Variable 2 = CALCULATE(MAX(Table1[Column 2]),ALLEXCEPT(Table1[Column 2]))

But these both give me 100. If I just have Max(Table1[Column 1]) then it returns a null value, since there are no rows where two two slicers conditions are valid on the same row.

I want to avoid splitting up the columns into separate tables, help would be appreciated. Thanks

[v-cherch-msft]

Hi @Anonymous

I would suggest you create a table with DISTINCT Function and use it as slicer.Then you may ingore the other filters to get the max value.

Table = DISTINCT(Table3[Bucket 1])

Bucket_1 = MAX('Table'[Bucket 1])

Regards,

Cherie

[Anonymous]

Hi @v-cherch-msft

Since I would need to do this for every column I wanted to slice I don't really see the benefit of doing this over splitting the original table up, which is what I had hoped to avoid doing in the first place.

The only difference I can see is that I'm doing the splitting at the formula stage rather than the initial database stage.

If I'm misunderstanding something please let me know.

Thanks

[v-cherch-msft]

Hi @Anonymous

It seems there's no better ways except 'Edit interactions'.

Regards,

Cherie