powerbidax

'Exclude' slicer in Power BI


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 :

enter image description here

Slicer selection :

enter image description here

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

Solution

  • 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

    enter image description here


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

    enter image description here