Create Filter to apply to matrix so it will only show compliant IDs

[MBisceglia]

Hi everyone, 

I'm currently working with employees' IDs. I'm trying to have a matrix that will only show those employees who worked more than 44 days every quarter that the Matrix is showing (based on the slicer selection). I already grouped all my data into quarters and added a column (Compliant Q) to identify those quarters for each ID that is compliant with this condition. This how the backend table in my model looks like:

I then used "Compliant Q" column to filter out the non-compliant quarters.

And this is how the Matrix looks like:

I would like to create a measure that could do what the Matrix's "Row Subtotal" and the slicer combined are doing. I want to have a measure that sums total number of compliant quarters by ID based on my selection, to use it as a filter for this page in my report. I want my matrix to only show those IDs in which the 12 selected quarters are compliant with the 44 days condition. I also want this to be dynamic, so it will update based on the user's selection. In case the user selects 20 quarters I would like the matrix to only show those IDs in which the 20 selected quarters are compliant with the 44 days condition and so on... In the example I shared the matrix is also showing IDs that have less than 12 compliant months. I want to avoid that.

Does anyone have any ideas? I would really appreciate the help!

Thanks in advance,

Michelle

[v-zhangti · Accepted solution]

Hi, @MBisceglia 

Maybe you can create a new measure to filter the displayed IDs.

Measure = IF(SELECTEDVALUE(WorkDays[Total worked days])>44,1,0)

Put the measure into the filter and set it equal to 1.

Of the 4 quarters selected, only this one id was eligible. Please check the attachment, does it match your desired result? What other values would you like to achieve, preferably with pictures to show them.

Best Regards,

Community Support Team _Charlotte

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

[tamerj1]

Hi @MBisceglia 

please try

Compliant Q 2 =
IF (
    SUM ( WorkDays[Total worked days] )
        = COUNTROWS ( ALLSELECTED ( WorkDays[Year-Quarter] ) ),
    1,
    BLANK ()
)