Forum Discussion
DISTINCTCOUNT with condition
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
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.
7 Replies
COUNTROWS(
SUMMARIZE(
FILTER(data,data[status]="not complete"),
[id]
)
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
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.
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é
