Concatenex with Rank

[Jyaul1122]

Hello friend, 

I have Stage table with fields: Project,Stage and Date.

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

I would  like to get table where , Stage  falls in which year so that I wrote measure like based on the date order:

Project	2024	2025	2026	2027
Project 1	S1,S2,S3,S4,S5	S6	S7
Project 2			S1,S2,S3	S4,S5
Project 3		S2,S1	S3,S4	S5,S7,S5

Stage measure = CONCATENATEX('Stage table',CALCULATE(MAX('Stage table'[Stage]),'Stage table'[Date]<=MAX('Stage table'[Date])),",",'Stage table'[Date],ASC)

But actually I would like to get table with combination of rank by date in individual year like: Both Rank  Stage :

ex: 1 S2, 2 S1 -- 1,2 represent rank and S2,S1 represent Stage   

Project	2024	2025	2026	2027
Project 1	1 S1,2 S2,3 S3,4 S4,5 S5	1 S6	1 S7
Project 2			1 S1,2 S2,3 S3	1 S4,2 S5
Project 3		1 S2, 2 S1	1 S3,2 S4	1 S5,2 S7,2 S5

how can i combine Rank inside Concatenax or do you have any idea by DAX ?

[Jyaul1122 · Accepted solution]

@DataNinja777 @Kedar_Pande 

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
)

[Jyaul1122]

@Kedar_Pande @DataNinja777 

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

[DataNinja777]

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,

[Kedar_Pande]

@Jyaul1122 

Add a calculated column in your Stage table to extract the year from the date:

Year = YEAR('Stage table'[Date])

Create a Rank Measure

RankStage = 
RANKX(
FILTER(
'Stage table',
'Stage table'[Project] = MAX('Stage table'[Project]) &&
YEAR('Stage table'[Date]) = YEAR(MAX('Stage table'[Date]))
),
'Stage table'[Date],
, ASC,
DENSE
)

Create the Combined Measure:

CombinedStageRank = 
CONCATENATEX(
FILTER(
'Stage table',
'Stage table'[Project] = MAX('Stage table'[Project]) &&
YEAR('Stage table'[Date]) = MAX('Stage table'[Year])
),
RANKX(
FILTER(
'Stage table',
'Stage table'[Project] = MAX('Stage table'[Project]) &&
YEAR('Stage table'[Date]) = YEAR(MAX('Stage table'[Date]))
),
'Stage table'[Date],
, ASC,
DENSE
) & " " & 'Stage table'[Stage],
", ",
'Stage table'[Date],
ASC
)

Create a Matrix Visual:

Use the Project field for the Rows.
Use the Year field for the Columns.
Use the CombinedStageRank measure for the Values.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[DataNinja777]

Hi @Jyaul1122 ,

You can produce your required output by writing a measure like below after creating a calendar table (DateTable), and creating a relationship between your fact table and the calendar dimension table.

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'[Date],
                ,
                ASC
            ), "0"
        ) & " " & 'Stage table'[Stage]
    ),
    [Rank_Stage],
    ", ",
    'Stage table'[Project],
    ASC
)

The resulting output will be as shown below, which is in line with your required output. 

I've attached an example pbix file for your reference.

Best regards,

[Jai-Rathinavel]

Hi @Jyaul1122 , I have achieved your expected output as below, have attached the PBIX file along with this post

Download PBIX

Did I answer your question ? If yes, please mark this post as a solution.

Thanks,

Jai 

[Jyaul1122]

Thanks for your reply, we are very close to solution.

Could you please sort by rank, for example in Project 3, rank 1 will be appear first with stage, then second rank with stage and so on

[Jai-Rathinavel]

@Jyaul1122 , It can be done by just introducing an orderby field (Table[Rank]) to the CONCATENATEX funtion. Replace the current measure expression with the below dax. 

Concat = 
var res = CONCATENATEX('Table', 'Table'[Rank] & " " & 'Table'[Stage],",",'Table'[Rank])
RETURN
res

Output:

If the DAX meets your expectation, please mark this post as a solution

Thanks,

Jai