Trouble with filtering measure

[schwandt4]

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.

[PaulDBrown · Accepted solution]

With these measures:

Available Hours by active users = 
SUMX(
    ADDCOLUMNS(
        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...

[schwandt4]

This is exactly what I need.  Thank you!

[Calvin69]

HI @schwandt4 ,

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

Regards

H

[schwandt4]

Only option is to share file in Google Drive.

[Calvin69]

Hey Budd @schwandt4 ,

Thanks for sharing that.

This should resolve your query:

Thanks

H

[schwandt4]

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"