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] )
)
)