Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.!
@sree_12 , 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])) ))
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])) ))
Hi @Anonymous i created mesure like this
// 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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
21 | |
20 | |
15 | |
13 |
User | Count |
---|---|
45 | |
41 | |
39 | |
19 | |
19 |