Preventing certain rows from being filtered in calculated measure

[dataforhumans]

I have a measure to be calculated that should respond to slicers on the page, but it should never filter out a certain subset of rows denoted by a boolean column.

So for the table:

Value	Color	is_filterable
1	Yellow	TRUE
2	Green	TRUE
3	Blue	TRUE
10	Red	FALSE
10	Orange	FALSE

the result of sum_measure that sums the Value column but never filters rows where is_filterable = FALSE should look like this:

sum_measure	Color
21	Yellow
22	Green
23	Blue
20	Red
20	Orange

How can this be achieved?

[v-zhouwen-msft]

Hi all,thanks for the quick reply, I'll add more.

Hi @dataforhumans ,

Regarding your question, you want the line items to be affected by the slicer, but the sum not to be affected by the slicer, correct?

The Table data is shown below:

You can use the 'Calculate' function to override the outer filter condition with the inner filter condition.

Measure = CALCULATE(SUM('Table'[Value]),'Table'[is_filterable] = TRUE() || 'Table'[is_filterable] = FALSE())

Final output

If I understand you wrongly, please provide more information.

Best Regards,
Wenbin Zhou

[danextian]

Hi @dataforhumans ,

Creating a measure will  return a  value like as if those rows are not filtered but they will not be visible.  For example, calculating YTD value as of 2023/12/30 - a measure can return the value for that but once you select that date from a slicer only the selected date will be visible.  That's how related colums and tables work. A disconnected table might abe able to achieve this but your post just doesn't have enough information to propose a solution. Please refer to this post on how to get your question answered quickly - https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447... 

[hnguy71]

Hi @dataforhumans 

The following removes the filter context from your measure: You can try this:

CALCULATE([sum_measure], ALL(is_filterable))