DAX COUNTIF equivalent, based on aggregated results-CodePudding

I have been racking my brain on this, I have a report that I need to get a count of different thresholds from the result of a measure. My data is daily, but the report needs to be at the weekly level. When I have tried to do a = CALCULATE ( COUNT( AgentData[AgentName] ), FILTER ( ALLEXCEPT( AgentData, AgentData[Start of Week], AgentData[AgentName] ), [AHT] < 600 )

This seems to count all days in the week where [AHT] < 600, however I am looking for a count of all agents in the week where [AHT] < 600. AHT is calculated using the fields AgentData[HandleTime] / AgentData[Chats]

The result I'm looking for, as an aggregate of weekly data. Can this be done? Ideally, the time interval would be based on what is selected in the pivot, and would work if at the daily, weekly, monthly level.

Week	Total Agents	< 600
Week 1	100	25
Week 2	125	40

Sample data:

Date	Agent Name	HandleTime	Chats
5/1/2022	Bob	6000	10
5/2/2022	Bob	4800	11
5/1/2022	Lucia	5200	8

Total Agents is a distinctcount of AgentData[AgentName]

CodePudding user response：

Remove AgentData[AgentName] from ALLEXCEPT, replace COUNT with TotalAgents measure (if that one should be used for) and it should work properly:

a = 
CALCULATE ( 
   [TotalAgents], 
   FILTER ( 
      ALLEXCEPT( AgentData, AgentData[Start of Week] ), 
      [AHT] < 600 
   )
)