Help with multiple entries from users and unique values

[Tor]

Hello. 

I have a problem with a new database and would like a helping hand. 

Database: To summarize, my database consists on a list of Action plans, activities and advance towards completing this activities. 

• An action plan is created and this contains multiple activities that must be completed. 
• Activities must be completed by anyone from a large group of people before specific date. 
• Each time anyone reports an "advance" a new entry to the list is added until activity is completed and eventually, until action plan is completed. 

Problem:

• Action plans have a unique code, but activities dont have an unique code.
• I must count activities on due for each person, but since i have an entry for each activity advance, i count due activities multiple times. 

Database example:

Action plan code	Action plan name	Action plan responsible	Activity	Activity responsible	Activity due date	Activity advance	Activity status
1	AP1	Peter	Activity 1	Tony	11-feb-23	null	Open
1	AP1	Mary	Activity 2	Jess	20-feb-23	Advance 1	Open
1	AP1	Mary	Activity 2	Jess	20-Feb-23	Advance 2	Open

I think, i may need to create a unique code to identify activities, but unsure how to do this on M in Power Query, or in DAX. 

Any help would be appretiated. 

Tor

[edhans · Accepted solution]

Not 100% sure I understand the ask. But, if you add the person responsible to a visual in Power BI, then create a measure as follows:

Activity Count = DISTINCTCOUNT(TableName[Activity])

That will give you a distinct count of the activities for that user. So Mary will have 1 since she has "Activity 2" twice. 

[Tor]

Thank you for your response!

This actially worked perfectly. 

I was looking for an extra column while transforming data and did not think of a measure as suggested. 

This allowed to me to count the total number of activities that Mary is pending to complete.

Thank you for your time! 

[edhans]

Glad I was able to help @Tor . 
I always look for a measure first as it adds no data to the model, so is the most efficient way to report info back to the user.