I want to create an ‘exclude’ slicer in power bi to filter and display records that do not have the slicer selected values in my visuals.
Sample dataset :
Slicer selection :
If I select ‘FD’ in my slicer, only OrderID: B456, C789, D111 that do not have the ‘FD’ StatusCode will show up in my matrix visual.
I tried these codes in my measure but it does not work.
FilterOrderIDs =
COUNTROWS (
FILTER (
dataset,
NOT (
CONTAINSSTRING (
'dataset'[StatusCode],
SELECTEDVALUE ( 'dataset'[StatusCode] )
)
)
)
)
Table 2 = DISTINCT('Table'[StatusCode])
and do not create relationship between these two tables
then create a measure
MEASURE =
VAR _list =
CALCULATETABLE (
DISTINCT ( 'Table'[StatusCode] ),
ALLEXCEPT ( 'Table', 'Table'[OrderID] )
)
RETURN
IF ( SELECTEDVALUE ( 'Table 2'[StatusCode] ) IN _list, 0, 1 )
and add this measure to the visual filter and set to 1
you can try this to count the order number
MEASURE 2=
DISTINCTCOUNT ( 'Table'[OrderID] )
- CALCULATE (
DISTINCTCOUNT ( 'Table'[OrderID] ),
FILTER (
'Table',
'Table'[StatusCode] = SELECTEDVALUE ( 'Table 2'[StatusCode] )
)
)
3
For this to work, your slicer cannot be using the same table as your data table.
Create a new Calculated Table with:
Dim StatusCode = DISTINCT('dataset'[StatusCode])
And use this one for your slicer. Then update your measure to:
FilterOrderIDs =
var slicerValues = DISTINCT('Dim StatusCode'[StatusCode])
var orderIDs =
CALCULATETABLE(
DISTINCT('dataset'[OrderID]),
'dataset'[StatusCode] IN slicerValues
)
return
CALCULATE(
COUNTROWS('dataset'),
NOT 'dataset'[OrderID] IN orderIDs,
REMOVEFILTERS('Dim StatusCode')
)
2