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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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...


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?
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 ) )

Helpful resources


Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.