Forum Discussion

filipeoliveira's avatar
filipeoliveira
Icon for Helper I rankHelper I
4 years ago
Solved

DISTINCTCOUNT based on two rows from another column

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...
  • amitchandak's avatar
    amitchandak
    4 years ago

    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])

     

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

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:] )