DAX or MCode formula help: copy cell in row until change in row

[Anonymous]

Hi, 

I need a DAX (or M) formula to replicate the data in column [TEXT3]. Let's call this table 'Table1'. 

I want to copy the text in column [TEXT1] until there is a change triggered by "MAKE" in [TEXT2].

Maybe usage of EARLIER or SWITCH in this case?   

TEXT1	TEXT2	TEXT3
12AS	MAKE	12AS
34AB	BUY	12AS
12C	BUY	12AS
12D	BUY	12AS
99	MAKE	99
B24	BUY	99
B33	BUY	99
A12	BUY	99
102	MAKE	102
C1	BUY	102
C2	BUY	102
C3	BUY	102
33	MAKE	33
3A	BUY	33
5A	BUY	33

[Anonymous]

Or, I can always add an Index Column as well to force the order of column [TEXT1]

[v-diye-msft · Accepted solution]

Hi @Anonymous ,

I've added the index column , then use below calculated column:

Column = VAR a = 'Table'[Index]
RETURN
 CALCULATE (MAX ( 'Table'[TEXT1] ),FILTER (ALL ( 'Table' ),'Table'[Index]= CALCULATE (MAX ( 'Table'[Index] ),FILTER ( ALL ( 'Table' ), 'Table'[Index] <= a && 'Table'[TEXT2] = "MAKE" ))))

Best regards,

Dina Ye

[Anonymous]

Now I'm having an issue when implementing. 

Error : "The function MAX takes an argument that evaluates to numbers or dates and cannot work with values of type String."

= VAR a =BOMs[Index]
RETURN

CALCULATE (MAX ( BOMs[PART NO] ),
FILTER (ALL (BOMs ),
BOMs[Index]= CALCULATE (MAX ( BOMs[Index] ),
FILTER ( ALL ( BOMs ), BOMs[Index] <= a && BOMs[MAKE OR BUY] = "MAKE" ))))

[Anonymous]

I'm in PowerPivot so I wonder if I need to use MAXX instead of MAX (?)

[Anonymous]

Thanks so much!

[Anonymous]

Hi @Anonymous ,

I think for this function, the sort of your table is very important, could you please tell me the column that you sort the table, and what if user change the sort in the table?

Aiolos Zhao

[Anonymous]

Column [TEXT1] would be the ascending sort I guess.