Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello, I have been trying to create a count of tests done by employees but there are certain parameters I want to include. They are required to do 2 per week, and I need to create a count to see how many of these mandatory tests have been completed. I can't just use the COUNT or SUM functions because this counts the total number done and if an employee has done more than 2, it throws off the total. Basically. I want to create a measure that counts the number of tests for each employee, and if it is 2 or higher, only count 2. I also can't do IF(COUNT(emplyee name)>2,2,COUNT(employee name)) because this is checking if the count of ALL employees is greater than 2, and i need to check for individual employees. My data table is a list of tests done, so emplyee names will be repeated. I could create a new table of employee names and create a calculated column based off of the counts of tests done, but I wanted to see if anyone knew how to achieve this with a measure.
Solved! Go to Solution.
Hi @Anonymous ,
According to your description, I create a sample.
Here's my solution, create a measure.
Count =
VAR _T =
ADDCOLUMNS (
'Table',
"Count",
IF (
COUNTROWS (
FILTER ( 'Table', 'Table'[Employee Nmae] = EARLIER ( 'Table'[Employee Nmae] ) )
) > 2,
2
/ COUNTROWS (
FILTER ( 'Table', 'Table'[Employee Nmae] = EARLIER ( 'Table'[Employee Nmae] ) )
),
1
)
)
RETURN
SUMX ( _T, [Count] )
Get the correct result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
According to your description, I create a sample.
Here's my solution, create a measure.
Count =
VAR _T =
ADDCOLUMNS (
'Table',
"Count",
IF (
COUNTROWS (
FILTER ( 'Table', 'Table'[Employee Nmae] = EARLIER ( 'Table'[Employee Nmae] ) )
) > 2,
2
/ COUNTROWS (
FILTER ( 'Table', 'Table'[Employee Nmae] = EARLIER ( 'Table'[Employee Nmae] ) )
),
1
)
)
RETURN
SUMX ( _T, [Count] )
Get the correct result.
I attach my sample below for your reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , You need measures like
Test count = count(Table[Test])
test GT 2 = Sumx(filter(Values(Table[Employee]),[Test Count]>2), [Test Count])
test GT 2 cnt = Countx(filter(Values(Table[Employee]),[Test Count]>2), [Employee])
weekly test GT 2 = Sumx(filter(Summarize(Table, Table[Employee], Table[Week], "_1",[Test Count]) , [_1 ]>2), [_1])
Could you explain the "_1" to me?
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |