cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ryvius Frequent Visitor
Frequent Visitor

How to ignore external filters for specific columns.

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:

 

image.pngTable

Report Page:

 

image.png

4 REPLIES 4
Ryvius Frequent Visitor
Frequent Visitor

Measure that ignores page filters that affect specific columns. ALLEXCEPT not working as expected.

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:image.pngTwo 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

Community Support Team
Community Support Team

Re: How to ignore external filters for specific columns.

Hi @Ryvius

 

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])

1.png

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ryvius Frequent Visitor
Frequent Visitor

Re: How to ignore external filters for specific columns.

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

Highlighted
Community Support Team
Community Support Team

Re: How to ignore external filters for specific columns.

Hi @Ryvius

 

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

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.