cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
schwandt4
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).

 

schwandt4_0-1630502798717.png

 

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).

 

schwandt4_2-1630503189153.png

 

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

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...

result.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

6 REPLIES 6
PaulDBrown
Super User
Super User

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...

result.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






This is exactly what I need.  Thank you!

Calvin69
Helper III
Helper III

HI @schwandt4 ,

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

Regards

H

Only option is to share file in Google Drive.

 

 

 

Hey Budd @schwandt4 ,

Thanks for sharing that.

This should resolve your query:

Calvin69_0-1630509057407.png

 

Thanks

H

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.

 

schwandt4_0-1630509620168.png

 

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

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

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

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

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!

MPPC 2023 PBI Carousel

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