Filter matrix rows using a measure without affecting totals

[BG919]

I have a matrix with a row hierarchy of:

State

City

Facility

Job Family

Job Title

I have a headcount column which is a measure:

Headcount = CALCULATE(

COUNTROWS('Dim_Org Structure'))

I need to apply a visual filter to hide rows where the headcount is less than 5. So if a job title has fewer than 5 employees, it doesn't show up in the matrix, but those employees are still counted in the job family, facility, city, state subtotal rollups. Or if an entire job family has fewer than 5 employees at a given facilty, it doesn't show, but is still reflected for facility headcount, etc.

But I also still need to be able to slice by department so that HR, Finance, Manufacturing, etc. can see their headcount across facilities.  I have tried various SWITCH, ALLEXCEPT, ISINSCOPE, HASONEFILTER combinations from other threads, which let me keep my unfiltered hierarchy subtotals while hiding rows, but they in turn are not responsive to the department slicer. 

[BG919]

Thanks! I figured it would be something simple, but I was stuck.

[VahidDM · Accepted solution]

Hi @BG919 

Can you share a sample of your data and the result you are looking for?

BTW, you can add If statment to show blank when the headcount is less than 5.

Try this:

Headcount =
VAR _V =
    CALCULATE ( COUNTROWS ( 'Dim_Org Structure' ) )
RETURN
    IF ( _V < 5, BLANK (), _V )

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!