VAR with a CountRows and adding a filter out for another column

[Anonymous]

Hello, I have the following code and am looking to add a filter to it to remove inputs in another column.  I've filtered for, but never against and never from a different column, so I'm a little out of my element. Any help would be greatly appreciated.

Active = VAR __CurrentRowID = Main[Employee ID]

RETURN

COUNTROWS(

FILTER(

ALL(Main),

__CurrentRowID = Main[mgr_emplid]

)

)

The column name is: Main[Position].  I have over 400 values in it, but would like to filter out the following values so that the column returns a count of everything else. 

Remove:

Team Member

Team Member - Nights

Team Member - Nights - DO

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

As the official document said, ALL() function returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.

Please try the following formula to add a column:

Active =
VAR __CurrentRowID = Main[Employee ID]
RETURN
    CALCULATE (
        COUNTROWS ( 'Main' ),
        FILTER (
            'Main',
            'Main'[mgr_emplid] = __CurrentRowID
                && NOT ( [Position]
                IN { "Team Member", "Team Member - Nights", "Team Member - Nights - DO" } )
        )
    )

The final output is shown below:

Here is the pbix file.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , not very clear you can use all or removefilters

refer: https://www.linkedin.com/pulse/five-recent-power-bi-functions-you-should-use-more-often-amit-chandak

[Anonymous]

I read your article, and maybe I'm misunderstanding or I wasn't clear.  I'm trying to add a filter to the command to filter out certain values.