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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors