Forum Discussion

Jyaul1122's avatar
Jyaul1122
Icon for Helper III rankHelper 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
)

     

     

Jyaul1122's avatar
Jyaul1122
Icon for Helper III rankHelper III
1 year ago

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's avatar
Jai-Rathinavel
Icon for Super User rankSuper User
1 year ago

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