Display Not selected values from Slicer (Consider selections from other slicer)

[Anonymous]

Hello,

I need your help in creating dax measure to display values which are not selected in a slicer.

Customer	Year
A	2019
B	2019
C	2019
D	2019
B	2020
A	2020
A	2021

I have two slicers, year and customer. when user select a year and customer then I need to display in a table where customers are not selected.

for example slicer selection Year: 2019, customers: A B C then result should be  D

Example 2: Year: 2020 customers: A then result: B

If no customers selected/ all selected then table should be empty.

Please let me know how to resolve this

Thank you.!

[amitchandak]

@Anonymous , Try

new table = except(filter(all(table[customer]),table[Year] in values(Table[Year])), allselected(table[customer]))

or a measure

new measure = calculate(count(Table[customer]), filter(all(Table[customer]), Table[customer] in except(filter(all(table[customer]),table[Year] in values(Table[Year])), allselected(table[customer])) ))

[Anonymous]

Hello @amitchandak , I am unable to use [Year] column in the dax(highlighted) you have mentioned

calculate(count(Table[customer]), filter(all(Table[customer]), Table[customer] in except(filter(all(table[customer]),table[Year] in values(Table[Year])), allselected(table[customer])) ))

[Anonymous]

Just put a table visual on the canvas, put slicers on as well, edit the interactions between the table and slicers, so that the slicer(s) does not interact with the table. Then create a measure that will return 1 for a row (customer?) that should be shown and 0 otherwise, and then filter the rows in the visual by this measure in the Filter Pane. Easy.

[Anonymous]

Hi @Anonymous i created mesure like this 

measure=iF(SELECTEDVALUE(Table[customers])="TRUE", 1,0) and I applied visual level filter, but its bot working as expected.

[Anonymous]

OK. Seems that you'll have to create a table with just customer names and it'll be disconnected. This is the table that you'll use for the table I was talking about. Then you'll use a measure to display what you want. I'll past the measure in a sec...

[Anonymous]

// Assumption:
// You've put a table on the canvas that
// holds Customers (their names or IDs).
// The source for this table will be a
// disconnected table that will only hold
// all the names of the customers. 
// You can create such a calculated table with
// this command:

[Excluded Customers] = // table
	distinct(
		T[Customers]
	)

// Say the table's name is 'Excluded Customers'.
// You also have 2 slicers that come from
// the T table.
//     1. Customer Names
//     2. Years
// Here's a measure that can be used for
// the table 'Excluded Customers' above to show
// the customers which have not been selected in the
// slicer.

[Should Show?] =
var __custCount =
	DISTINCTCOUNT( T[Customer] )
var __totalCustCount =
	CALCULATE(
		DISTINCTCOUNT( T[Customer] ),
		ALL( Customer )
	)
var __shouldFilter =
	__custCount < __totalCustCount
var __oneCustVisibleInExcludedCustomers =
	HASONEFILTER( 'Excluded Customers'[Customer] )
var __oneYearSelected =
	HASONEFILTER( T[Year] )
var __shouldShow = true()
	&& __oneYearSelected
	&& __shouldFilter
	&& __oneCustVisibleInExcludedCustomers
var __result =
	if( __shouldShow,
		1 * NOT (
			SELECTEDVALUE( DC[Customer] )
			IN VALUES( T[Customer] )
		)
	)
return
	__result

[IWallis]

does this work for you? I was trying to solve a similar problem. But the code in the end is confusing, is there another table DC (this part of code -- 

SELECTEDVALUE( DC[Customer] )

 )

and also this part 

	ALL( Customer )

, does this one belong to any table? Thank you!