DAX syntax help for COUNT number of open items using 2 tables

[adam2695]

Hi there,

I have 3 tables in an extended data model, which I am trying to use DAX to calculate something and I am struggling to figure it out...

Table 1 - User Groups

Table 2 - ServiceNow Problem

Table 3 - ServiceNow Problem Tasks

I am trying to calculate the number of Tasks that do not have an assignee of the Problems that are owned by a usergroup. I can do this via a calculated column using the following:-

countPTasksUnassigned =

CALCULATE(COUNTROWS(sn_task_fact),FILTER(sn_task_fact,sn_problem_fact[sys_id] = sn_task_fact[problem] && NOT(sn_task_fact[taskState] in {"New"}))

)

That works totally fine because the problem sys_id is entered into the filter, however I am stumped on how I can do this using just a measure, dynamically...

The following DAX can be used if I include the Problem Record in the table and reference it via SelectedValue:-

Count PTasks Closed Problem Linked =

CALCULATE(COUNT(sn_task_fact[number]),FILTER(sn_task_fact,sn_task_fact[ProblemId] = SELECTEDVALUE(sn_incident_fact[problemId]) && sn_task_fact[taskState] = "Closed"))

However, is it possible in DAX to dynamically look at all of the Problems that are assigned to a specific team without including the Problem in the table?

[johnt75]

You could do something like

Num unassigned =
VAR Problems =
    VALUES ( 'sn_problem_fact'[problem ID] )
VAR Tasks =
    VALUES ( 'sn_task_fact'[problem ID] )
VAR Result =
    COUNTROWS ( EXCEPT ( Problems, Tasks ) )
RETURN
    Result