cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
laxmikantpatil
Frequent Visitor

Count number of unique items

I have two tables as below

Name  Target

a            3

b            2  

c            1

d            2

 

Name Job Status

a           x      Complete

a           y     Incomplete

b          z       Incomplete

a          w      Incomplete

b          u       Complete

c          o        Complete

d          p        Incomplete

d          l         Complete

 

Now I want a new column in table to count number of completed jobs by a person and in the second column no. of incomplete jobs. (Just the count) So that I can see what percent of job is completed.

 

Please help.

Thank You

2 ACCEPTED SOLUTIONS

Without more details of your model or data I would guess you have a typo in your table and column reference.  Let the intellsense complete the phrase when referencing the needed column to be sure it is correct rather than typing it.  

 

here is the demo file I made for your example in the orig post.  Not getting the same error you show... 

 

https://www.dropbox.com/s/z2ghfczksfxzd1l/SolveCompletedDIstinct.pbix?dl=0

 

 

 

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

@laxmikantpatil  - to get the % you can add this measure:

Percent Complete = 
DIVIDE(
    CALCULATE(COUNTROWS(YourTable), YourTable[Status] = "Complete"),
    COUNTROWS(YourTable)
)
I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Nathan Peterson

Hi it doesn't give me the exact count corresponding to the right name.

I am getting this warning

PBI.PNG

Can you share your file? Or at least an image of your data model? Hard to debug as I dont know your model or data set

Without more details of your model or data I would guess you have a typo in your table and column reference.  Let the intellsense complete the phrase when referencing the needed column to be sure it is correct rather than typing it.  

 

here is the demo file I made for your example in the orig post.  Not getting the same error you show... 

 

https://www.dropbox.com/s/z2ghfczksfxzd1l/SolveCompletedDIstinct.pbix?dl=0

 

 

 

View solution in original post

Great Thank You

It worked

Thanks a million

Yes this works.

Can you tell me how did you calculate no. of completed jobs.

I have calculated but it took 5 steps of editing.

Please let me know if there is a simple way.

See the bottom of the image....

 

Completed Jobs = CALCULATE(DISTINCTCOUNT(Results[Job]),Results[Status]="Complete")

PS - please mark this as solved if the solution is helpful! 

 

Cheers! 

I have two different data tables.

Will this be valid for two different data sets too?

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.