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

     

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.