Positive and negative filter

[jvandyck]

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 negative filter on the usage of salary codes in order to be able to show companies using specific codes and not using specific codes. In power bi I can only create positive filters, but there has to be a workaround for this. Who can help me? 

[jvandyck]

Here you can find a mock-up with a positive slicer and a negative slicer: https://acerta-my.sharepoint.com/:f:/g/personal/joos_van_dyck_acerta_be/ErGyDBRA9L5GlQVkN-tKoM4BGidi.... The test data is in the excel. 

If I want to show companies with code 1 and not 2, it should return company A.

If I want to show companies with code 4 and code 8, it should return companies B and C.

If I want to show companies without code 2, it should return A and C.

If I want to show companies with code 3 but not code 3, it should return A and C.

This logic should be expandable to multiselect both in the positive and in the negative filter.

[v-yiruan-msft · Accepted solution]

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

[jvandyck]

important is that I can add multiple codes both on the positive and the negative side

[mahenkj2]

Hi @jvandyck ,

I would suggest you to add a sample of desired output.

[amitchandak]

@jvandyck , You can create two measures and create calculation groups

or a new column

if([salary] <0, "Negative", "Positive")

[jvandyck]

thank you for your swift reply...but how can I then add 2 listbars, listing all salary codes, one as the positive filter and one as the negative filter to select the codes I want to use in my positive and negative filter?

[amitchandak]

@jvandyck , If this does not help
Can you share sample data and sample output in table format?

But I think calculation group can help

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display: https://youtu.be/qMNv67P8Go0