Forum Discussion
Solved
Distinct Count Goal as completed when all Task Status is marked as completed
Good Day BI generals, what approach and dax function can i use to solve this challenge on my subject (distinct count Goal as completed when all Task is marked as completed ), Below is a dummy table i have generated to guide you. Thanks
for example all tasks in Web goal has Status as Completed so it will count as 1 Goal and not two.
Greg_Deckler amitchandak wdx223_Daniel ryan_mayu Please i need this solution, thanks in anticipation
Dummy Table
| TEAMS | PROJECT | QUARTER | GOALS | TARGETS | TASKS | Task STATUS |
| Data | Project 1 | Q1 | Goal 1 | Sample target | Data cleaning | Completed |
| Data | Project 1 | Q1 | Goal 1 | Sample target | Data viz | In Progress |
| Data | Project 1 | Q1 | Goal 1 | Sample target | Dashboard | In Progress |
| Data | Project data | Q1 | Goal 2 | Sample targ | Data restructure | Completed |
| Data | Project data | Q1 | Goal 2 | Sample targ | Data Collection | Completed |
| Data | Project data | Q1 | Goal 3 | Sample targ | Discovery | In Progress |
| Web development | Web project 1 | Q2 | Web goal | Target | Site dev | Completed |
| Web development | Web project 1 | Q2 | Web goal | Target | App dev | Completed |
| Web development | Web sample project | Q2 | Sample goal | New target | Code review | In Progress |
| Web development | Web sample project | Q2 | Sample goal | New target | Documatation review | Completed |
This is the expected output
| SUB TEAMS | PROJECT | QUARTER | Total Unique goals | Completed goals |
| Data | Project data | Q1 | 2 | 1 |
| Data | Project 1 | Q1 | 1 | 0 |
| Web development | Web project 1 | Q2 | 1 | 1 |
| Web development | Web sample project | Q2 | 1 | 0 |
pls try this
unique goal = DISTINCTCOUNT('Table'[GOALS]) Complete goals = [unique goal]-CALCULATE(DISTINCTCOUNT('Table'[GOALS]),FILTER('Table','Table'[Task STATUS]<>"Completed"))
2 Replies
pls try this
unique goal = DISTINCTCOUNT('Table'[GOALS]) Complete goals = [unique goal]-CALCULATE(DISTINCTCOUNT('Table'[GOALS]),FILTER('Table','Table'[Task STATUS]<>"Completed"))
