Forum Discussion
Multi-condition table
Hello PBI community,
I have a problem which requires (re)building a table with multiple columns based on multiple conditions per column.
Problem statement:
- I have a database which contains multiple projects in the organization. Each project goes through 5 stages: 1 - Concept, 2 - Feasibility, 3 - Execution, 4 - Closure, Completed. Every update of the project, be it an update on the content of the phase or when changing the phase generates a new entry in the database, with its corresponding date of the update. This means that each project creates multiple entries in the database.
What I try to achieve:
- I want to create a new table which will have the following columns:
- 1. List of projects (unique)
- 2. Date when the project got into 1 - Concept
- 3. Days that the project stayed in 1 - Concept
- 4. Date when the project got into 2 - Feasibility
- 5. Days that the project stayed in 2 - Feasibility
- ... same logic for the rest of the phases
- I then want to find out:
- how many projects do I have in the pool (total)? how many projects do I have in each stage?
- what is the actual velocity of the projects? how long do they stay in a certain stage
- what is the average velocity of the projects? how long do they stay on average in a certain stage
- what is the conversion rate relative to Stage 1 - Concept? e.g. how many projects made it to Stage 2, 3, 4, Completed (in percentage) - in the current data table the answer will be 100% since all made it to the end but I would like to know the logic/formula
- what is the conversion rate relative to the previous stage? e.g. how many projects from stage 1 - Concept made it to stage 2 - Feasibility, how many from Feasibility made it to Execution etc. - in the current data table the answer will be 100% since all made it to the end but I would like to know the logic/formula
- since some projects spill into the next year, I would like to show that some projects have been completed in 2021 while others in 2022
- how would everything above look like if I add one more dimension - Project type (Software, Hardware, Infrastructure)
Where I am today (and caveats):
- The output table is done manually, I visually inspect the data and copy/paste the date when the project changed stages
- I was able to get in PowerBI only Column 1 by creating a new table and using DISTINCT('Source'[Project name])
Source table:
| Project name | From Stage | To Stage | Date | Proj Mgr. |
| Alpha | 1 - Concept | 01/03/2021 | John | |
| Bravo | 1 - Concept | 01/04/2021 | Mike | |
| Alpha | 1 - Concept | 1 - Concept | 10/03/2021 | John |
| Alpha | 1 - Concept | 1 - Concept | 12/03/2021 | John |
| Charlie | 1 - Concept | 01/09/2021 | Ann | |
| Delta | 1 - Concept | 01/12/2021 | Ann | |
| Delta | 1 - Concept | 2 - Feasibility | 01/12/2021 | Ann |
| Delta | 2 - Feasibility | 3 - Execution | 01/12/2021 | Ann |
| Delta | 3 - Execution | 4 - Closure | 20/12/2021 | Ann |
| Delta | 4 - Closure | Completed | 20/01/2022 | Ann |
| Alpha | 1 - Concept | 2 - Feasibility | 02/04/2021 | John |
| Alpha | 2 - Feasibility | 2 - Feasibility | 20/04/2021 | John |
| Bravo | 1 - Concept | 1 - Concept | 05/04/2021 | Mike |
| Bravo | 1 - Concept | 2 - Feasibility | 10/05/2021 | Mike |
| Charlie | 1 - Concept | 2 - Feasibility | 01/11/2021 | Ann |
| Alpha | 2 - Feasibility | 3 - Execution | 05/05/2021 | John |
| Alpha | 3 - Execution | 3 - Execution | 09/09/2021 | John |
| Alpha | 3 - Execution | 4 - Closure | 10/10/2021 | John |
| Charlie | 2 - Feasibility | 3 - Execution | 15/12/2021 | Ann |
| Charlie | 3 - Execution | 3 - Execution | 10/01/2022 | Ann |
| Charlie | 3 - Execution | 3 - Execution | 20/01/2022 | Ann |
| Charlie | 3 - Execution | 3 - Execution | 01/03/2022 | Ann |
| Bravo | 2 - Feasibility | 2 - Feasibility | 15/05/2021 | Mike |
| Alpha | 4 - Closure | Completed | 15/11/2021 | John |
| Bravo | 2 - Feasibility | 2 - Feasibility | 01/06/2021 | Mike |
| Bravo | 2 - Feasibility | 1 - Concept | 01/07/2021 | Mike |
| Bravo | 1 - Concept | 1 - Concept | 10/07/2021 | Mike |
| Bravo | 1 - Concept | 2 - Feasibility | 20/07/2021 | Mike |
| Bravo | 2 - Feasibility | 3 - Execution | 18/08/2021 | Mike |
| Bravo | 3 - Execution | 3 - Execution | 10/09/2021 | Mike |
| Bravo | 3 - Execution | 4 - Closure | 01/10/2021 | Mike |
| Bravo | 4 - Closure | Completed | 15/10/2021 | Mike |
| Charlie | 3 - Execution | 4 - Closure | 10/04/2022 | Ann |
| Charlie | 4 - Closure | Completed | 15/05/2022 | Ann |
Output table:
| Project | 1 - Concept | Days in Concept | 2 - Feasibility | Days in Feasibility | 3 - Execution | Days in Execution | 4 - Closure | Days in Closure | Completed | PM |
| Alpha | 01/03/2021 | 32 | 02/04/2021 | 33 | 05/05/2021 | 158 | 10/10/2021 | 36 | 15/11/2021 | John |
| Bravo | 01/04/2021 | 39 | 10/05/2021 | 100 | 18/08/2021 | 44 | 01/10/2021 | 14 | 15/10/2021 | Mike |
| Charlie | 01/09/2021 | 61 | 01/11/2021 | 44 | 15/12/2021 | 116 | 10/04/2022 | 35 | 15/05/2022 | Ann |
| Delta | 01/12/2021 | 0 | 01/12/2021 | 0 | 01/12/2021 | 19 | 20/12/2021 | 31 | 20/01/2022 | Ann |
Hi, Anonymous
You can try the following methods.
Table = SUMMARIZE('Source table','Source table'[Project name],'Source table'[Proj Mgr.])Column:
1 - Concept = CALCULATE(MIN('Source table'[Date]),FILTER('Source table',[To Stage]="1 - Concept"&&[Project name]=EARLIER('Table'[Project name])))2 - Feasibility = CALCULATE(MIN('Source table'[Date]),FILTER('Source table',[To Stage]="2 - Feasibility"&&[Project name]=EARLIER('Table'[Project name])))Days in Concept = [2 - Feasibility]-[1 - Concept]Change the column format to integer.
3 - Execution = CALCULATE(MIN('Source table'[Date]),FILTER('Source table',[To Stage]="3 - Execution"&&[Project name]=EARLIER('Table'[Project name])))Days in Feasibility = [3 - Execution]-[2 - Feasibility]4 - Closure = CALCULATE(MIN('Source table'[Date]),FILTER('Source table',[To Stage]="4 - Closure"&&[Project name]=EARLIER('Table'[Project name])))Days in Execution = [4 - Closure]-[3 - Execution]Completed = CALCULATE(MIN('Source table'[Date]),FILTER('Source table',[To Stage]="Completed"&&[Project name]=EARLIER('Table'[Project name])))Days in Closure = [Completed]-[4 - Closure]Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi, Anonymous
You can try the following methods.
Table = SUMMARIZE('Source table','Source table'[Project name],'Source table'[Proj Mgr.])Column:
1 - Concept = CALCULATE(MIN('Source table'[Date]),FILTER('Source table',[To Stage]="1 - Concept"&&[Project name]=EARLIER('Table'[Project name])))2 - Feasibility = CALCULATE(MIN('Source table'[Date]),FILTER('Source table',[To Stage]="2 - Feasibility"&&[Project name]=EARLIER('Table'[Project name])))Days in Concept = [2 - Feasibility]-[1 - Concept]Change the column format to integer.
3 - Execution = CALCULATE(MIN('Source table'[Date]),FILTER('Source table',[To Stage]="3 - Execution"&&[Project name]=EARLIER('Table'[Project name])))Days in Feasibility = [3 - Execution]-[2 - Feasibility]4 - Closure = CALCULATE(MIN('Source table'[Date]),FILTER('Source table',[To Stage]="4 - Closure"&&[Project name]=EARLIER('Table'[Project name])))Days in Execution = [4 - Closure]-[3 - Execution]Completed = CALCULATE(MIN('Source table'[Date]),FILTER('Source table',[To Stage]="Completed"&&[Project name]=EARLIER('Table'[Project name])))Days in Closure = [Completed]-[4 - Closure]Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It's working almost as expected. The reason for saying "almost" is that I missed 3 scenarios:
1. A project can jump stages - for example project Echo entered 1 - Concept on the 1st of Jan and was moved directly into 3 - Execution on the 1st of Feb. This means it didn't go through 2 - Feasibility so the entry in 2 - Feasibility column is empty therefore columns Days in Concept and/or Days in Feasibility will fail the calculation
2. A project can enter directly in 3 - Execution, which means the 1 - Concept and 2 - Feasibility will be empty so the Days in Concept/Feasibility will fail the calculation
3. Since I am running the report/dashboard today, some projects may be in different stages - e.g. project Golf can be in 1 - Concept. In this scenario, Days in Concept should be Today() - [1 - Concept]
I think the scenarios above can be done with some IF nesting (or ISBLANK). I'll give it a go and see what comes up.
Either way, thank you very much for the solution, it works like a charm.
Best,
M