Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
adam2695
New Member

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

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...
 
adam2695_0-1688469420523.png

 

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?
1 REPLY 1
johnt75
Super User
Super User

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors