I'm struggling with a measure where ALLSELECTED provides me with a table containing the rows I need, but I need to apply some further filtering on it before I can apply CALCULATE to compute the total of visit duration (Table AgentActivities).
My issue is that sometimes after having selected the DATE and the AGENT_ID, some visits are spread over more than one row all sharing the same DATE, AGENT_ID, VISIT_ID and each having the same DURATION_IN_SECONDS for that same combination of DATE, AGENT_ID and VISIT_ID but each row with have a different description of ACTIONS_TAKEN.
Total duration of daily visits =
CALCULATE (
SUM( 'AgentActivities'[DURATION_IN_SECONDS] );
ALLSELECTED ( 'AgentActivities' )
)
The above measure works fine for all those cases wher there are no duplicated DATE, AGENT_ID, VISIT_ID, DURATION_IN_SECONDS.
So, I tried the following in an attempt to remove the 'AgentActivities'[ACTIONS_TAKEN] and hoping that then the duplicates sharing the same DATE, AGENT_ID, VISIT_ID and DURATION_IN_SECONDS would only show once so that the SUM would not include the duplicates.
Total duration of daily visits =
CALCULATE (
SUM( 'AgentActivities'[DURATION_IN_SECONDS] );
ALLEXCEPT ( 'AgentActivities'; 'AgentActivities'[ACTIONS_TAKEN] )
)
I don't have a report I could share at this point.
Any suggestions will be appreciated!
