Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
I'm looking for assistance in creating a measure. I have a dataset of timecard details by employee. I have a DAX measure where I am identifying employees who have used a specific code for less than 70% of their time recorded. That measure is below.
Solved! Go to Solution.
Hi @mg09
You can use the following measure to determine if Employees Utilization is less than 0.7.
IF Employees Utilization < 70% = --Number of employees who've used the utilization COA less than 70% of their time recorded
VAR _Emp = SELECTEDVALUE('Emp Example'[Employee])
VAR _Hours = SUMX(FILTER(ALL('Emp Example'),'Emp Example'[Employee] = _Emp),'Emp Example'[Hours])
VAR _JZU0_Hours = SUMX(FILTER(ALL('Emp Example'),'Emp Example'[Employee] = _Emp && 'Emp Example'[COA] = "JZU0"),'Emp Example'[Hours])
RETURN
IF(DIVIDE(_JZU0_Hours,_Hours)<0.7,1,0)
Measure 2 = CALCULATE(DISTINCTCOUNT('Emp Example'[Employee]),FILTER(ALL('Emp Example'),[IF Employees Utilization < 70%] = 1))
Result:
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @mg09
All you need to do is put the following measure into the filter.
IF Employees Utilization < 70% = --Number of employees who've used the utilization COA less than 70% of their time recorded
VAR _Emp = SELECTEDVALUE('Emp Example'[Employee])
VAR _Hours = SUMX(FILTER(ALL('Emp Example'),'Emp Example'[Employee] = _Emp),'Emp Example'[Hours])
VAR _JZU0_Hours = SUMX(FILTER(ALL('Emp Example'),'Emp Example'[Employee] = _Emp && 'Emp Example'[COA] = "JZU0"),'Emp Example'[Hours])
RETURN
IF(DIVIDE(_JZU0_Hours,_Hours)<0.7,1,0)
Result:
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @mg09
You can use the following measure to determine if Employees Utilization is less than 0.7.
IF Employees Utilization < 70% = --Number of employees who've used the utilization COA less than 70% of their time recorded
VAR _Emp = SELECTEDVALUE('Emp Example'[Employee])
VAR _Hours = SUMX(FILTER(ALL('Emp Example'),'Emp Example'[Employee] = _Emp),'Emp Example'[Hours])
VAR _JZU0_Hours = SUMX(FILTER(ALL('Emp Example'),'Emp Example'[Employee] = _Emp && 'Emp Example'[COA] = "JZU0"),'Emp Example'[Hours])
RETURN
IF(DIVIDE(_JZU0_Hours,_Hours)<0.7,1,0)
Measure 2 = CALCULATE(DISTINCTCOUNT('Emp Example'[Employee]),FILTER(ALL('Emp Example'),[IF Employees Utilization < 70%] = 1))
Result:
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I already have the correct measure calculation for the employees who's time is less than 70%. I'm looking at it from the perspective of if all their hours they've worked week over week is less than 70%, let me know who that is. That's why I'm using the values function.
What I then need is the details for JUST those people. And what I mean by details is the example on the Employee Details by COA page for JUST those 3 people. It's a matrix visual with their names and week ending date in the rows and the COA description in the columns.
Pink highlighted I already have, I'm trying to get to the yellow highlighted.
Hi @mg09
All you need to do is put the following measure into the filter.
IF Employees Utilization < 70% = --Number of employees who've used the utilization COA less than 70% of their time recorded
VAR _Emp = SELECTEDVALUE('Emp Example'[Employee])
VAR _Hours = SUMX(FILTER(ALL('Emp Example'),'Emp Example'[Employee] = _Emp),'Emp Example'[Hours])
VAR _JZU0_Hours = SUMX(FILTER(ALL('Emp Example'),'Emp Example'[Employee] = _Emp && 'Emp Example'[COA] = "JZU0"),'Emp Example'[Hours])
RETURN
IF(DIVIDE(_JZU0_Hours,_Hours)<0.7,1,0)
Result:
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, that solution worked.
@mg09 , First create a measure
Create a measure to calculate the utilization percentage for each employee:
Employee Utilization % =
DIVIDE(
[JZU0 Hours],
[Hours]
)
Create a measure to identify employees with utilization less than 70%:
DAX
Employees with Utilization < 70% =
CALCULATE(
COUNTROWS('Emp Example'),
FILTER(
VALUES('Emp Example'[Employee]),
[Employee Utilization %] < 0.70
)
)
Create a measure to filter the matrix visual based on the utilization condition:
DAX
Filtered Employees % by COA =
IF(
[Employee Utilization %] < 0.70,
[Employees % by COA],
BLANK()
)
Use the Filtered Employees % by COA measure in your matrix visual:
Proud to be a Super User! |
|
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
15 | |
14 | |
11 | |
7 |
User | Count |
---|---|
26 | |
24 | |
12 | |
11 | |
10 |