Forum Discussion
Jyaul1122
1 year agoHelper III
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...
- 1 year ago
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
1 year agoHelper III
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
)