Filter Matrix Entries by Sub Total of Measure

[Anonymous]

Hi Everyone,

currently i am struggling with a filter Option in my Report.

I have following Matrix:

I try to visualize the Ammount of Days, where an employye tracked time, grouped by the last three weeks. If an employee tracked more than five days, there Measure "isCritical" switches to 1. My Goal is, to show only those employees, who have three critical weeks in line (in other words: where the sum of isCritical = 3).

In this example, only employee 2 should be visible. I tried to work with COUNTROWS, but i don't get the expected Result. In my thougts, i need something like a filter option on the level of every subTotal.

Does someone has a proposal to solve the problem? Thanks for your help!

[Anonymous · Accepted solution]

For now i found following work around solution:

I just store the total subSum in every row with following DAX:

sumIsCritical = CALCULATE([isCritical],ALL(DimDate[WeekOfYear]))

This allows me to filter all rows, where the value is 3 👍

[jthomson]

What does the underlying data look like?

[Anonymous]

 

The whole data Model is much more complicated, but this should visualize the required tables in an abstract way. In Power BI I have an calculated column, which filter the last three weeks. TrackedDays is calculated by a distinct rowCount on daily Base where trackedTimes != 0.