Forum Discussion

Jaypearce's avatar
Jaypearce
Frequent Visitor
1 year ago
Solved

Power Bi Fomula - Exclude rows if other column is filtered

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" & ...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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. 

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Jaypearce 

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