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
)

     

     

Jai-Rathinavel's avatar
Jai-Rathinavel
Super User
1 year ago

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's avatar
    Jyaul1122
    Helper 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
      Super 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