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) the id should not be counted.

 

 

So it should count MER2021073, LOG2021051 and CGM2021032 only.

 

Someone could help?

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

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    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:] )
  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.