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 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? 

Need Help
  • 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

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    jvandyck , You can create two measures and create calculation groups

     

    or a new column

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

    • jvandyck's avatar
      jvandyck
      Helper IV
      4 years ago

      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's avatar
        amitchandak
        Super User
        4 years ago

        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

  • jvandyck's avatar
    jvandyck
    Helper IV
    4 years ago

    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-tKoM4BGiditjPRw2wHFRdNh_dxiw?e=WqeeWa. 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.

     

    • jvandyck's avatar
      jvandyck
      Helper IV
      4 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      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