cancel
Showing results for
Did you mean:
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
Super User

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
Super User

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

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors