Forum Discussion
DISTINCTCOUNT with condition
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.
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
Community ChampionI 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.