Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

7 REPLIES 7
jefe5
New Member

hello, I cant use your solution,,  here is my table, mame Tableau2 

I want to count number CDPs distinct whith contition=Retard, result=2

TI test 

(SUMMARIZECOLUMNS(Tableau2[Nom CDPs],FILTER(Tableau2[Etat])="Retard")))
Nom CDPsEtat
Creation nouveaux bureaux , broyeur, zone decheterieSoldé
Creation nouveaux bureaux , broyeur, zone decheterieSoldé
Creation nouveaux bureaux , broyeur, zone decheterieRetard
Creation nouveaux bureaux , broyeur, zone decheterieRetard
Creation nouveaux bureaux , broyeur, zone decheterieRetard
Creation nouveaux bureaux , broyeur, zone decheterieRetard
nouvelles référencesRetard
nouvelles référencesEn cours
nouvelles référencesEn cours
nouvelles référencesEn cours
t imprimanteSoldé
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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors