Forum Discussion
Help with multiple entries from users and unique values
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
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.
3 Replies
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.
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!
