Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to Solution.
Hi @JPSCHAFFER ,
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
Or, I can always add an Index Column as well to force the order of column [TEXT1]
Hi @JPSCHAFFER ,
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
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" ))))
I'm in PowerPivot so I wonder if I need to use MAXX instead of MAX (?)
Thanks so much!
Hi @JPSCHAFFER ,
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
Proud to be a Super User!
Column [TEXT1] would be the ascending sort I guess.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |