Forum Discussion

Jyaul1122's avatar
Jyaul1122
Helper III
1 year ago
Solved

Concatenex with Rank

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 S...
Need Help
  • Jyaul1122's avatar
    Jyaul1122
    1 year ago

    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
)

     

     

Kedar_Pande's avatar
Kedar_Pande
Super User
1 year ago

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