DISTINCTCOUNT based on two rows from another column

[filipeoliveira]

Hi,

I'm trying to do a DISTINCTCOUNT on the ID column below, but it should count only if there are DRAFT and FINAL status values for the same ID, if there is only one status (be it draft or final) the id should not be counted.

So it should count MER2021073, LOG2021051 and CGM2021032 only.

Someone could help?

[v-robertq-msft]

Hi, @filipeoliveira 

According to your description, I can clearly understand your requirement, you want to count the amount of the ID if the status is less than 2, right? I think you can create a single measure to use a virtual table like this to achieve your requirement:

Count =

var _table=SUMMARIZE(DISTINCT('Table'),[ID],"Count",DISTINCTCOUNT('Table'[STATUS]))

return

COUNTX(FILTER(_table,[Count]<2),[ID])

And you can create a card chart to get what you want, like this:

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi,

please check the below picture and the attached pbix file.

Distinct Count Draft and Final: =

VAR currentid =

MAX ( Data[ID] )

VAR currentidtable_draft =

FILTER ( ALL ( Data ), Data[ID] = currentid && Data[STATUS] = "DRAFT" )

VAR currentidtable_final =

FILTER ( ALL ( Data ), Data[ID] = currentid && Data[STATUS] = "FINAL" )

VAR conditions =

IF (

COUNTROWS ( currentidtable_draft ) >= 1

&& COUNTROWS ( currentidtable_final ) >= 1,

1

)

RETURN

conditions

Total fix: =

SUMX( VALUES( Data[ID] ), [Distinct Count Draft and Final:] )

[amitchandak · Accepted solution]

@filipeoliveira , use this measure with id

Countx(filter(summarize(Table, Table[ID], "_1", calculate(Distinctcount(Table[Status]), filter(Table, Table[Status] in {"DRAFT","FINAL"}) )),[_1]=2),[ID])