Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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
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.
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 value
ProjectC | 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?
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
88 | |
82 | |
66 | |
64 | |
57 |
User | Count |
---|---|
171 | |
111 | |
109 | |
72 | |
71 |