Forum Discussion
Concatenex with Rank
I solved by editing above measures.
First I created one column Sorting in Power query from date column and stage
Date.Year([Date])*100000000
+Date.Month([Date])*1000000
+Date.Day([Date])*10000
+Text.End([Stage],1)and measure edit:
Stage Measure = VAR CurrentYear = SELECTEDVALUE('DateTable'[Year]) -- Gets the selected year from the DateTable VAR Projects = DISTINCT('Stage table'[Project]) -- Get distinct projects for iteration RETURN CONCATENATEX( ADDCOLUMNS( FILTER( 'Stage table', YEAR('Stage table'[Date]) = CurrentYear ), "Rank_Stage", FORMAT( RANKX( FILTER( 'Stage table', 'Stage table'[Project] = EARLIER('Stage table'[Project]) && YEAR('Stage table'[Date]) = CurrentYear ), 'Stage table'[Sorting], , ASC ), "0" ) & " " & 'Stage table'[Stage] ), [Rank_Stage], ", ", 'Stage table'[Date], ASC )
Thanks for your time.
The replies are close to solution, what if the two or more stage have same dates. For example Project 4: Stage 3 and Stage 5 are have same date. As per your logic I am getting rank 1 for both but I would like to get:
| Project 4 | 1 S1, 2 S2 | 1 S3, 2 S5 | ||
Data:
| Project | Stage | Date |
| Project 1 | S1 | 25-Dec-24 |
| Project 1 | S2 | 27-Dec-24 |
| Project 1 | S3 | 27-Dec-24 |
| Project 1 | S4 | 28-Dec-24 |
| Project 1 | S5 | 29-Dec-24 |
| Project 1 | S6 | 06-Jan-25 |
| Project 1 | S7 | 09-Jan-26 |
| Project 2 | S1 | 10-Jan-26 |
| Project 2 | S2 | 16-Jan-26 |
| Project 2 | S3 | 17-Jan-26 |
| Project 2 | S4 | 18-Jan-27 |
| Project 2 | S5 | 21-Jan-27 |
| Project 3 | S1 | 27-Jan-25 |
| Project 3 | S2 | 25-Jan-25 |
| Project 3 | S3 | 31-Jan-26 |
| Project 3 | S4 | 04-Feb-26 |
| Project 3 | S5 | 05-Feb-27 |
| Project 3 | S6 | 29-Mar-27 |
| Project 3 | S7 | 13-Feb-27 |
| Project 4 | S1 | 14-May-26 |
| Project 4 | S2 | 15-May-26 |
| Project 4 | S3 | 16-May-27 |
| Project 4 | S5 | 16-May-27 |
Hi Jyaul1122 ,
To address the case where multiple stages for a project have the same date and ensure unique rankings (e.g., assigning a secondary order within the same date), we need to enhance the ranking logic. This can be achieved by considering the Stage column as a secondary sorting criterion in the RANKX function.
Here’s the revised measure:
Stage Measure =
VAR CurrentYear = SELECTEDVALUE('DateTable'[Year]) -- Selected year context
RETURN
CONCATENATEX(
ADDCOLUMNS(
FILTER(
'Stage table',
YEAR('Stage table'[Date]) = CurrentYear
),
"Rank_Stage",
FORMAT(
RANKX(
FILTER(
'Stage table',
'Stage table'[Project] = EARLIER('Stage table'[Project]) &&
YEAR('Stage table'[Date]) = CurrentYear
),
'Stage table'[Date] & 'Stage table'[Stage],
,
ASC
),
"0"
) & " " & 'Stage table'[Stage]
),
[Rank_Stage],
", ",
'Stage table'[Project],
ASC
)
The resulting output is as shown below:
The output respects both the date and stage ordering criteria.
I've attached an example pbix file for your reference.
Best regards,