Power Bi Fomula - Exclude rows if other column is filtered

[Jaypearce]

Hi all,

Hi all,

I have am wondering if anyone can help me with this semi odd formula.

Basically I want to have a filter for when "cost center" is filtered for multiple items (e.g. "CC2" & CC8"), If the filtered values appears on any rows in "Partner cost center" then that row would be excluded. I have given some large summary data and power bi file. But I cannot work out what I am doing wrong.

https://drive.google.com/drive/folders/1ze0vl4z6aCvf_Z2snzUamM9A7a30qp9N?usp=drive_link

Thanks

[danextian · Accepted solution]

Hi @Jaypearce 

Create a disconnected table of cost centre and use a measure to visual filter your table.

Cost Centre = 
VALUES('Input Data'[Cost Center]) --Calc Table

Exclude Filter = 
VAR _count =
    COUNTROWS ( 'Cost Centre' )  // count how many cost centres are selected
VAR _isfiltered =
    ISFILTERED ( 'Cost Centre'[Cost Center] )  // check if slicer is filtered at all
VAR _result =
    SWITCH (
        TRUE (),

        // if only one selected or nothing selected, return full count (no exclusion)
        _count = 1 || NOT _isfiltered,
        COUNTROWS ( 'Input Data' ),

        // if multiple are selected, exclude them from the input data
        _isfiltered && _count > 1,
        COUNTROWS (
            EXCEPT (
                VALUES ( 'Input Data'[Partner Cost Center] ),   // all partner cost centres
                VALUES ( 'Cost Centre'[Cost Center] )           // exclude selected cost centres
            )
        )
    )
RETURN
    _result

Please see the attached pbix. 

[v-priyankata]

Hi @Jaypearce 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

[v-priyankata]

Hi @Jaypearce 

Hope everything’s going smoothly on your end. We haven’t heard back from you, so I wanted to check if the issue got sorted  If yes, marking the relevant solution would be awesome for others who might run into the same thing.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[v-priyankata]

Hi @Jaypearce 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@SundarRaj @danextian Thanks for the inputs.
In addition to their input, please try below DAX & drag the Exclude Filter measure into the visual's Filters pane and set it to show only rows where the value is 1. let me know if you are still experiencing the issue.

Exclude Filter =

VAR SelectedCCs = VALUES(Summary[Cost Center])

VAR CurrentCC = SELECTEDVALUE(Summary[Cost Center])

VAR PartnerCC = SELECTEDVALUE(Summary[Partner Cost Center])

RETURN

    IF (

        NOT ISBLANK(CurrentCC) &&

        NOT ISBLANK(PartnerCC) &&

        CurrentCC IN SelectedCCs &&

        NOT PartnerCC IN SelectedCCs,

        1,

        0

    )

Thanks

[v-priyankata]

Hi @Jaypearce 

I hope the information provided was helpful. If you still have questions, please don't hesitate to reach out to the community.

[danextian · Accepted solution]

Hi @Jaypearce 

Create a disconnected table of cost centre and use a measure to visual filter your table.

Cost Centre = 
VALUES('Input Data'[Cost Center]) --Calc Table

Exclude Filter = 
VAR _count =
    COUNTROWS ( 'Cost Centre' )  // count how many cost centres are selected
VAR _isfiltered =
    ISFILTERED ( 'Cost Centre'[Cost Center] )  // check if slicer is filtered at all
VAR _result =
    SWITCH (
        TRUE (),

        // if only one selected or nothing selected, return full count (no exclusion)
        _count = 1 || NOT _isfiltered,
        COUNTROWS ( 'Input Data' ),

        // if multiple are selected, exclude them from the input data
        _isfiltered && _count > 1,
        COUNTROWS (
            EXCEPT (
                VALUES ( 'Input Data'[Partner Cost Center] ),   // all partner cost centres
                VALUES ( 'Cost Centre'[Cost Center] )           // exclude selected cost centres
            )
        )
    )
RETURN
    _result

Please see the attached pbix. 

[SundarRaj]

Hi @Jaypearce ,

Still working on the DAX solution of it. But did figure out a way to do what you desire on Power Query. I'll attach the sample file which has the output. Do let me know if this is what you wanted. Thanks!

https://docs.google.com/spreadsheets/d/1SquOy_Bpyyml1rcpwygMmAOtlWU3I1kz/edit?usp=sharing&ouid=10475...

Picture above is the filter that you were talking about and how to navigate the selection. Thanks