DISTINCTCOUNT with condition

[Anonymous]

I have data as shown belew 

id	stage	status
1	a	complete
1	b	complete
1	b	not complete
2	a	complete
2	b	not complete

I want to count number of id based on not complete status

so I have apply this Measure:

Measure = CALCULATE(
DISTINCTCOUNT(data[id]),FILTER(data,data[status]="not complete"))

but as you can see Id 1 have go to stage b tow times one with status complete and the other not complete,

so becouse I am focusing on not complete ids, I do not need to count the id if it on the same stage and with status complete.

based on the example the count of not complete will be 2 ,
but i need it to be 1 because i don't need to count id 1 if it is already complete on the same stage

[jefe5]

hello, I cant use your solution,,  here is my table, mame Tableau2 

I want to count number CDPs distinct whith contition=Retard, result=2

TI test 

(SUMMARIZECOLUMNS(Tableau2[Nom CDPs],FILTER(Tableau2[Etat])="Retard")))

Nom CDPs	Etat
Creation nouveaux bureaux , broyeur, zone decheterie	Soldé
Creation nouveaux bureaux , broyeur, zone decheterie	Soldé
Creation nouveaux bureaux , broyeur, zone decheterie	Retard
Creation nouveaux bureaux , broyeur, zone decheterie	Retard
Creation nouveaux bureaux , broyeur, zone decheterie	Retard
Creation nouveaux bureaux , broyeur, zone decheterie	Retard
nouvelles références	Retard
nouvelles références	En cours
nouvelles références	En cours
nouvelles références	En cours
t imprimante	Soldé

[Greg_Deckler]

COUNTROWS(

  SUMMARIZE(

     FILTER(data,data[status]="not complete"),

    [id]

  )

[Anonymous]

thank you 
but it is still the same , i need it first before count to check if the id with same stage have status complete if yes then, do not count it 
if NO, count it.

i need to do this becouse there is a duplicate on id with same stage , so to solve it i have to ignore the id on not complet if it complet with same stage.

hope it is clear

[HotChilli · Accepted solution]

I think this will work but please test properly with your data:

MeasureX = VAR _tabNot = SUMMARIZECOLUMNS(data[id], data[stage], FILTER(data,data[status] = "not complete"))
           VAR _tabComp = SUMMARIZECOLUMNS(data[id], data[stage], FILTER(data,data[status] = "complete"))
RETURN 
    COUNTROWS(_tabNot) - COUNTROWS(NATURALINNERJOIN(_tabComp, _tabNot))

Each of the variables holds a table with the id and stage of the different statuses.

Count the 'not completed' rows. Subtract from this the count  (of matching id and stage) rows that have a 'complete' and 'not complete'  status.

[Anonymous]

is there any way to return the result as a table?

[HotChilli]

What would the table look like?

[Anonymous]

thank you it is working