To calculate the values of the non-selected options of a slicer, I can create a new table (option 1) or use only 1 table (option 2) and since, both options give different results, it will depend on what I want. Alternatively, this article Power BI: exclude a value from the calculation explains another way to do it. I have this data:

For option 1, I will create a new table in order to extract the unique values of the “support” and “type” column:

NOTE:

• To know how, read Power BI: extract columns from a table to another one
• There is no relationship between my tables

Option 1, I will create a measure with this formula:

var result=CALCULATE(COUNT('table1'[argument1]),KEEPFILTERS(EXCEPT(ALL('table1'[argument2]),ALLSELECTED('table2'[argument1]))))
return
IF(ISFILTERED('table2'[argument1]),result,COUNT('table1'[argument1]))

Option 2, I will use this one:

var result=CALCULATE(COUNT('table1'[argument1]),FILTER(ALL('table1'),NOT('table1'[argument2]) IN VALUES('table1'[argument2])))
return
IF(ISFILTERED('table1'[argument2]),result,COUNT('table1'[argument1]))

NOTE:

• Change “table” and “argument” by yours
• IN VALUES() doesn’t accept condition, in this case, I will use IN CALCULATETABLE(). For instance, I want to exclude blank rows: IN CALCULATETABLE(VALUES('table1'[argument2]),NOT(ISBLANK('table1'[argument2])))

Now that I have everything set, for option 1, I will create a slicer with the new table and those 3 visuals (1 without and 2 with the measure):

If I select “cx”:

• The left visual is doing nothing because the measure is not included
• The middle visual, which has only the measure, is calculating everything excluding the selected option
• The right visual, which has the measure but also other information, is also calculating everything excluding the selected option. Moreover, I can see empty cells for the measure

With the option 2, this is the result:

• The middle and right tables with the measure, I can see that for each row, the result already excludes the support but not for the total row
• Once I use the slicer, the total row shows the result without them
• This option is more suitable for a “card” visual

If my slicer has multiple layers, I will have to use this formula for the option 1:

var allexc1=EXCEPT(ALL('table1'[argument2]),ALLSELECTED('table2'[argument1]))
var allexc2=EXCEPT(ALL('table1'[argument3]),ALLSELECTED('table2'[argument2]))
var result=IF(ISFILTERED('table2'[newsupport]),CALCULATE(COUNT('table1'[argument1]),KEEPFILTERS(allexc1)),CALCULATE(COUNT('table1'[argument1]),KEEPFILTERS(allexc2)))
return
IF(ISFILTERED('table2'[argument1])||ISFILTERED('table2'[argument2]),result,COUNT('table1'[argument1]))
              

For option 2:

var result=CALCULATE(COUNT('table1'[argument1]),FILTER(ALL('table1'),NOT('table1'[argument2]) IN VALUES('table1'[argument2])||NOT('table1'[argument3]) IN VALUES('table1'[argument3])))
return
IF(ISFILTERED('table1'[argument2])||ISFILTERED('table1'[argument3]),result,COUNT('table1'[argument1]))
              

What I am going to explain below, it is only for option 1. If I create a relationship, the formula will stop to work so to remediate it, I will include in the formula the CROSSFILTER function:

If you remember, my left visual didn’t change and the right one showed empty cell. The solution only works without relationship so I will create a new measure:

• For a single layer filter

SWITCH(TRUE(),VALUES('table1'[argument2]) IN VALUES('table2'[argument1]) && COUNTROWS(ALLSELECTED('table2'[argument1]))<>COUNTROWS(ALL('table2'[argument1])),1,0)              
              

• For a multiple layers filter

var allexc1=SWITCH(TRUE(),VALUES('table1'[argument1]) IN VALUES('table2'[argument1]) && COUNTROWS(ALLSELECTED('table2'[argument1]))<>COUNTROWS(ALL('table2'[argument1])),1,0)
var allexc2=SWITCH(TRUE(),VALUES('table1'[argument2]) IN VALUES('table2'[argument2]) && COUNTROWS(ALLSELECTED('table2'[argument2]))<>COUNTROWS(ALL('table2'[argument2])),1,0)
return
IF(ISFILTERED('table2'[argument1]),allexc1,allexc2)
              

Once done, for each visualization that doesn’t contain the measure and/or I want to make disappear the empty cells, I will add it in the “filters” panel by configuring like that:

NOTE: for the middle visualization, I don’t add it because it contains only the measure