Forum Discussion
Super UserTop n With matrix and date
Hi everyone,
I'm facing an issue with Power BI & Matrice, I have some projects with task associated, I created a matrix in which I would like for each project to return the 2 with the earliest start date
So this a dataset sample
| Project Name | Task | Start Date |
| ProjectA | Python script | 01/01/2023 |
| ProjectA | Java script | 16/01/2023 |
| ProjectA | C# Script | 02/02/2023 |
| ProjectB | C++ Script | 05/03/2023 |
| ProjectB | C Script | 29/03/2023 |
| ProjectB | Ruby Script | 17/04/2023 |
| ProjectC | Powershell Script | 02/03/2023 |
| ProjectC | Rust Script | 11/04/2023 |
| ProjectC | JS Script | 17/05/2023 |
| ProjectC | Node Script | 01/06/2023 |
And I would like to get something like:
| ProjectA | ||
| Python script | 01/01/2023 | |
| Java script | 16/01/2023 | |
| ProjectB | ||
| C++ Script | 05/03/2023 | |
| C Script | 29/03/2023 | |
| ProjectC | ||
| Node Script | 01/02/2023 | |
| Powershell Script | 02/03/2023 |
Any idea of how could I achieve that?
I tried Top N but it returns me something weird
Many thanks in advance for your help
rnk = var a = CALCULATETABLE(SUMMARIZE('Table',[Task],"md",min([Start Date])),REMOVEFILTERS('Table'[Task])) return rank(SKIP,a,ORDERBY([md],ASC))You cannot rank duplicate items. The SUMMARIZE part removes that ambiguity
3 Replies
- lbendlin
rnk = RANK(SKIP,Allselected('Table'),ORDERBY('Table'[Start Date]),LAST,PARTITIONBY('Table'[Project Name]))Note this is done as a measure , just in case. If you don't expect user interaction you can change it to a calculated column.
- Cookistador
Thank you for your help
I still have a last blocking point 😕
If two tasks have the same name in a project, it doesn't work
For my ProjectC, I added the following valueProjectC Powershell Script 02/03/2023 ProjectC Rust Script 09/04/2023 ProjectC Rust Script 11/04/2023 ProjectC JS Script 17/05/2023 ProjectC Node Script 01/02/2023 So as you see, there are two tasks called Rust script, but the measure doesn't return any rank for this value
I tried a few things like creating a kind of sugorate key or adding another parameter in the order by but it doesn't work
Any clue of waht I can do?
- lbendlin
rnk = var a = CALCULATETABLE(SUMMARIZE('Table',[Task],"md",min([Start Date])),REMOVEFILTERS('Table'[Task])) return rank(SKIP,a,ORDERBY([md],ASC))You cannot rank duplicate items. The SUMMARIZE part removes that ambiguity
