Distinct count with grouping condition (DAX)

Question

I am having problems with a DAX query and I hope you can help me :)

I want to create a measure that counts in how many sprints (identified as Iteration Path in the table) a User Story (identified as Title) has been present. However, if the user story has been closed (identified as State) in any sprint I want the measure to return a 0.

Title	Iteration Path	State
Subscription order intake	Sprint 10	Active
Subscription order intake	Sprint 10	Closed
Subscription order intake	Sprint 10	New
Source of truth views for list prices and cost prices	Sprint 09	New
Source of truth views for list prices and cost prices	Sprint 10	Active
Source of truth views for list prices and cost prices	Sprint 10	New
Source of truth views for list prices and cost prices	Sprint 11	Active
Source of truth views for list prices and cost prices	Sprint 12	Active
Source of truth views for list prices and cost prices	Sprint 12	Closed
Sales order views	Sprint 06	Active
Sales order views	Sprint 08	Active
Sales order views	Sprint 09	Active
Sales order views	Sprint 10	Active
Sales order views	Sprint 11	Active
Sales order views	Sprint 11	Closed
Quotes with longer than 6 months delivery time.	Sprint 06	New
Project economy - Data model	Sprint 06	Active
PrimeServ assist - Data model	Sprint 06	Active
PrimeServ assist - Data model	Sprint 08	Active
PrimeServ assist - Data model	Sprint 09	Active
Prepare Work shop and kick-off	Sprint 09	New
Prepare Work shop and kick-off	Sprint 10	Active
Prepare Work shop and kick-off	Sprint 10	New
Prepare Work shop and kick-off	Sprint 11	Active
Prepare Work shop and kick-off	Sprint 12	Active

Just as an example, the return value for "Subscription order intake", "Source of truth views for list prices and cost prices" and "Sales order views" will be 0. However, the return value for "Quotes with longer than 6 months delivery time" will be 1 and for "PrimeServ assist - Data model" will be 3. That will make something like the following table:

Title	Measure output
Subscription order intake	0
Source of truth views for list prices and cost prices	0
Sales order views	0
Quotes with longer than 6 months delivery time.	1
Project economy - Data model	1
PrimeServ assist - Data model	3
Prepare Work shop and kick-off	4

I hope I explained myself correctly, thanks in advance!

Answer 1

Table looks like this:

Measure:

   Measure = 
VAR t = CALCULATETABLE(VALUES('Table'[Title]), 
    FILTER('Table', 'Table'[State] = "Closed")
)
RETURN

CALCULATE(
    DISTINCTCOUNT('Table'[Iteration Path]), 
    FILTER('Table', NOT ('Table'[Title] IN t))
)+0

Result: