remove duplicate based on specific value

[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 drop the duplicate rows based on stage and id columns but because they have different status and want to keep the one with "complete".

I have seen this measure but I don't know how to compare by stage too and how to make it keep specific value not the MIN

measure = 
SUMMARIZE(yourTablename, yourTablename[ID], "MinValue", MIN(yourTablename[status]) )

the result I expect 

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

[camargos88]

Hi @Anonymous ,

Please, if it worked mark this as a solution. So we can help other with the same question.

Thanks.

Ricardo

[Anonymous]

thank you it worked but is there any way to apply it with measure

[camargos88]

Hi @Anonymous ,

Yes,

try this code:

Measure =

VAR _qtdeStatus = CALCULATE(DISTINCTCOUNT('Table (2)'[status]); ALLEXCEPT('Table (2)'; 'Table (2)'[id]; 'Table (2)'[stage]))

RETURN IF(_qtdeStatus > 1; CALCULATE(DISTINCT('Table (2)'[status]); FILTER('Table (2)'; 'Table (2)'[status] = "Complete")); CALCULATE(DISTINCT('Table (2)'[status])))

Ricardo

[camargos88 · Accepted solution]

Hi @Anonymous ,

I've created this file as an example: Download PBIX 

Ricardo