cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

DISTINCTCOUNT with condition

I have data as shown belew 

 

idstagestatus
1acomplete
1bcomplete
1bnot complete
2acomplete
2bnot complete

 

I want to count number of id based on not complete status

so I have apply this Measure:

 

Measure = CALCULATE(
DISTINCTCOUNT(data[id]),FILTER(data,data[status]="not complete"))

 

but as you can see Id 1 have go to stage b tow times one with status complete and the other not complete,

so becouse I am focusing on not complete ids, I do not need to count the id if it on the same stage and with status complete.

 

based on the example the count of not complete will be 2 ,
but i need it to be 1 because i don't need to count id 1 if it is already complete on the same stage

1 ACCEPTED SOLUTION

I think this will work but please test properly with your data:

MeasureX = VAR _tabNot = SUMMARIZECOLUMNS(data[id], data[stage], FILTER(data,data[status] = "not complete"))
           VAR _tabComp = SUMMARIZECOLUMNS(data[id], data[stage], FILTER(data,data[status] = "complete"))
RETURN 
    COUNTROWS(_tabNot) - COUNTROWS(NATURALINNERJOIN(_tabComp, _tabNot))

Each of the variables holds a table with the id and stage of the different statuses.

Count the 'not completed' rows. Subtract from this the count  (of matching id and stage) rows that have a 'complete' and 'not complete'  status.

View solution in original post

6 REPLIES 6
Greg_Deckler
Super User
Super User

COUNTROWS(

  SUMMARIZE(

     FILTER(data,data[status]="not complete"),

    [id]

  )

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

thank you 
but it is still the same , i need it first before count to check if the id with same stage have status complete if yes then, do not count it 
if NO, count it.

i need to do this becouse there is a duplicate on id with same stage , so to solve it i have to ignore the id on not complet if it complet with same stage.

hope it is clear

I think this will work but please test properly with your data:

MeasureX = VAR _tabNot = SUMMARIZECOLUMNS(data[id], data[stage], FILTER(data,data[status] = "not complete"))
           VAR _tabComp = SUMMARIZECOLUMNS(data[id], data[stage], FILTER(data,data[status] = "complete"))
RETURN 
    COUNTROWS(_tabNot) - COUNTROWS(NATURALINNERJOIN(_tabComp, _tabNot))

Each of the variables holds a table with the id and stage of the different statuses.

Count the 'not completed' rows. Subtract from this the count  (of matching id and stage) rows that have a 'complete' and 'not complete'  status.

Anonymous
Not applicable

is there any way to return the result as a table?

What would the table look like?

Anonymous
Not applicable

thank you it is working

Helpful resources

Announcements
March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.