New Column or Measure required to display totals

[Anonymous]

Hi, I have two tables as below. Parent Tickets and Child tickets. From the below data Parent ticket PT2 has incorrect status. That is when all the child tickets are completed, the parent ticket cannot be in Active state. I am looking for count of parent tickets which are still active when their child tickets are in completed status. 

Parent Tickets data

Service Name	Parent Ticket Number	Ticket Status
IT Service 1	PT1	Active
IT Service 2	PT2	Active
IT Service 3	PT3	Active
IT Service 1	PT4	Closed

Child Tickets Data

Parent Ticket Number	Child Ticket Number	Child Ticket Status
PT1	CT1	InProgress
PT1	CT2	Completed
PT1	CT3	Planned
PT1	CT4	Completed
PT2	CT5	Completed
PT2	CT6	Completed
PT2	CT7	Completed
PT3	CT8	InProgress
PT4	CT9	Completed

I also have a Visual like below except the last column. I am looking for the highlighted column.

Service Name	Total no of Parent tickets	Total no of child tickets	Total no of incomplete child tkts	Total parent tickets with incorrect status
IT Service 1	2	5	2	0
IT Service 2	1	4	0	1
IT Service 3	1	1	1	0

My measure are as below for this visual:
Total no of incomplete child tickets = Calculate(DISTINCCOUNT(ChildTickets[Child Ticket Number]), filter(ChildTicket, ChildTickets[Status] in {InProgress, Planned}))

Please help me to get the required out as in highlighted column above

[SolomonovAnton]

hello ! 

I hope it will help you 

Total no of incomplete child tickets =

var _count = Calculate(DISTINCCOUNT(ChildTickets[Child Ticket Number]), filter(ChildTicket, ChildTickets[Status] in

{InProgress, Planned}))
var _complete = if (countrows(values('Child Tickets Data'[ Child Ticket Status])) =1 && isblank(filter('Child Tickets Data','Child Tickets Data'[ Child Ticket Status] = 'Completed')),true(),false())

var _result = if (and(min('Parent Tickets data'[Ticket Status])='Active', _complete), _count,0)

return _result

[Anonymous]

Hi @SolomonovAnton , thanks for quick response. I am getting an error as below:
"The expression refers to multiple columns. multiple columns cannot be converted to scalar value"

[SolomonovAnton]

I did bit changes in measure 

please check 

Total no of incomplete child tickets = 

var _count = Calculate(COUNT('Child Tickets Data'[Child Ticket Number]),filter('Child Tickets Data', 'Child Tickets Data'[Child Ticket Status] in{"Completed"}))
var _complete = if (countrows(filter(VALUES('Child Tickets Data'[Child Ticket Status]),'Child Tickets Data'[Child Ticket Status] = "Completed"))=1,true(),false())

var _result = if (and(min('Parent Tickets data'[Ticket Status])="Active", _complete), _count,0)

return _result

I get follow result 

[Anonymous]

Hi @SolomonovAnton , thanks again for quick response. The error is gone. But my question is different. I am looking for total number of parent tickets which have incorrect status. My expected output is shown in the last column ''Total parent tickets with incorrect status" of the  table below. Can you please help if it is possible. Thanks

Service Name	Total no of Parent tickets	Total no of child tickets	Total no of incomplete child tkts	Total parent tickets with incorrect status
IT Service 1	2	5	2	0
IT Service 2	1	4	0	1
IT Service 3	1	1	1	0

[SolomonovAnton]

try it 

Total no of incomplete child tickets = 

var _count = Calculate(COUNT('Parent Tickets data'[Parent Ticket Number]),filter('Child Tickets Data', 'Child Tickets Data'[Child Ticket Status] in{"Completed"}))
var _complete = if (and(countrows(VALUES('Child Tickets Data'[Child Ticket Status]))=1,min('Child Tickets Data'[Child Ticket Status])= "Completed"),true(),false())

var _result = if (and(min('Parent Tickets data'[Ticket Status])="Active", _complete), _count,0)

return _result

it's variant look like what you expect

if I answered at your question please mark topic at resolved 🙂