Forum Discussion
Solved
Conditional Index column in DAX
Hello, I need to create a new index column based on values from previous rows and I would appreciate some help here. This is an example of the type of data we have. We don't have access to the da...
Hi 21818 ,
Here are the steps you can follow：
1. Create calculated column.
rand = FORMAT( RAND(),"General Number")Rank = VAR __Item =[rand] VAR __Text = CONCATENATEX('Table',[rand],"|") VAR __Count = PATHLENGTH(__Text) VAR __Table = ADDCOLUMNS( GENERATESERIES(1,__Count,1), "__Item",PATHITEM(__Text,[Value]) ) VAR __TableFinal = SUMMARIZE(__Table,[__Item],"Index",MINX(FILTER(__Table,[__Item]=EARLIER([__Item])),[Value])) RETURN MINX(FILTER(__TableFinal,[__Item] = __Item),[Index])ID1 = IF( 'Table'[numdays]=0 ||'Table'[code] = "A",BLANK(), "ID"&""& RANKX(FILTER(ALL('Table'),'Table'[numdays]=EARLIER('Table'[numdays])),[Rank],,ASC))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi 21818 ,
Here are the steps you can follow：
1. Create calculated column.
rand =
FORMAT(
RAND(),"General Number")Rank =
VAR __Item =[rand]
VAR __Text = CONCATENATEX('Table',[rand],"|")
VAR __Count = PATHLENGTH(__Text)
VAR __Table =
ADDCOLUMNS(
GENERATESERIES(1,__Count,1),
"__Item",PATHITEM(__Text,[Value])
)
VAR __TableFinal =
SUMMARIZE(__Table,[__Item],"Index",MINX(FILTER(__Table,[__Item]=EARLIER([__Item])),[Value]))
RETURN
MINX(FILTER(__TableFinal,[__Item] = __Item),[Index])ID1 =
IF(
'Table'[numdays]=0 ||'Table'[code] = "A",BLANK(),
"ID"&""& RANKX(FILTER(ALL('Table'),'Table'[numdays]=EARLIER('Table'[numdays])),[Rank],,ASC))2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Many thanks Anonymous ,
When trying this solution I am stuck at the creation of the rank column as it is taking a long time without success. My current dataset has around 400K columns and I assume this could be the issue for this.
