Forum Discussion

BG919's avatar
BG919
Frequent Visitor
4 years ago
Solved

Filter matrix rows using a measure without affecting totals

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. 
Need Help
  • VahidDM's avatar
    VahidDM
    4 years ago

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



2 Replies

  • BG919's avatar
    BG919
    Frequent Visitor
    4 years ago

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

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

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