Forum Discussion

jvandyck's avatar
jvandyck
Helper IV
4 years ago
Solved

Positive and negative filter

I have a table with 2 fields listing company id and salary code id to indicate which company uses which salary codes. This is a big tabel with over 230million rows. I want to add a positive and a ne...
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi jvandyck ,

    I updated your sample pbix file(see attachment), please check if that is what you want.

    1. Create two dimension table use the field [Salary Code] of table Sheet1

    Positive codes = VALUES('Sheet1'[Salary Code])
    Negative codes = VALUES('Sheet1'[Salary Code])

    2. Apply the fields in above dimension tables on the slicers(Positive and Negative)

    3. Create a measure as below to judge if the enterprise should display or not

    Measure = 
    VAR _selent =
        SELECTEDVALUE ( 'Sheet1'[Enterprise] )
    VAR _positivecodes =
        ALLSELECTED ( 'Positive codes'[Salary Code] )
    VAR _negativecodes =
        ALLSELECTED ( 'Negative codes'[Salary Code] )
    VAR _count1 =
        CALCULATE (
            DISTINCTCOUNT ( 'Sheet1'[Enterprise] ),
            FILTER (
                'Sheet1',
                'Sheet1'[Enterprise] = _selent
                    && 'Sheet1'[Flag] = 1
                    && 'Sheet1'[Salary Code] = SELECTEDVALUE ( 'Negative codes'[Salary Code] )
            )
        )
    VAR _tab1 =
        CALCULATETABLE (
            VALUES ( 'Sheet1'[Enterprise] ),
            FILTER (
                'Sheet1',
                'Sheet1'[Enterprise] = _selent
                    && 'Sheet1'[Flag] = 1
                    && IF (
                        ISFILTERED ( 'Positive codes'[Salary Code] ),
                        'Sheet1'[Salary Code] IN _positivecodes,
                        1 = 1
                    )
            )
        )
    VAR _tab2 =
        CALCULATETABLE (
            VALUES ( 'Sheet1'[Enterprise] ),
            FILTER (
                'Sheet1',
                'Sheet1'[Enterprise] = _selent
                    && 'Sheet1'[Flag] = 1
                    && IF (
                        ISFILTERED ( 'Negative codes'[Salary Code] ),
                        'Sheet1'[Salary Code] IN _negativecodes,
                        1 = 1
                    )
            )
        )
    RETURN
        IF (
            NOT ( ISFILTERED ( 'Negative codes'[Salary Code] ) ),
            IF ( _selent IN _tab1, 1, 0 ),
            IF ( _selent IN EXCEPT ( _tab1, _tab2 ), 1, 0 )
        )

    4. Create a table visual with visual-level filter condition(Measure is 1)

    Best Regards