cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Trouble with filtering measure

I am trying to create a new measure that is filtered based on related results of another measure.  To help explain what I am trying to do I have created a simple model.

In this simple model I have 4 tables (Date, User, UserAvlHours, and WorkedHours).

In the model I have some basic measures.

-Available Hours = SUM(AvlHours)

-Worked Hours = SUM(WorkedHours)

-User Utilization = DIVIDE([Worked Hours],[Available Hours])

On any given date a User may or may not have "Worked Hours" but will always have "Available Hours" and "Employee Utilization" will be calcuated for each user (which has worked hours).

In this simple example you can see the Total Utilization % is 66.25 which is 53/80, what I am trying to figure out is how can I create a filtered measure of Available Hours, that will ONLY show for Users that have Worked Hours.  I would like to have the "Filtered Available Hours" only show for Users A - G and be blank for H, I, J.  This would bring the total of "Filtered Available Hours" to 56 and the new "Filtered User Utilization" would be 53 / 56 =94.6%

What this filtering does is it gives me the utilization of Users that have done work (but ignores users that have no worked hours).

Thank you.

1 ACCEPTED SOLUTION
Community Champion

With these measures:

``````Available Hours by active users =
SUMX(
SUMMARIZE(
FILTER(UserAvlHours, NOT(ISBLANK([Worked Hours]))), User[UserID], 'Date'[Date]),
"ActiveHours", [Available Hours]),
[ActiveHours])``````
``````User Utilization by active users =
DIVIDE([Worked Hours], [Available Hours by active users])``````

you will get...

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

6 REPLIES 6
Community Champion

With these measures:

``````Available Hours by active users =
SUMX(
SUMMARIZE(
FILTER(UserAvlHours, NOT(ISBLANK([Worked Hours]))), User[UserID], 'Date'[Date]),
"ActiveHours", [Available Hours]),
[ActiveHours])``````
``````User Utilization by active users =
DIVIDE([Worked Hours], [Available Hours by active users])``````

you will get...

In doing so, you are also helping me. Thank you!

Proud to be a Super User!

Frequent Visitor

This is exactly what I need.  Thank you!

Helper III

HI @schwandt4 ,

Any chance you can share a copy of that PBIX file? or sample of some sort?

Regards

H

Frequent Visitor

Only option is to share file in Google Drive.

Helper III

Hey Budd @schwandt4 ,

Thanks for sharing that.

Thanks

H

Frequent Visitor

This would only work for a particular visual and when the User is brough in the rows.  If I remove "User" from the matrix the "Available Hours" will go back to 80.

What I I need is a measure that will filter out "Available Hours" when a user is not present in the "Worked Hours"

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.