Forum Discussion
Split rows into several columns
Hey guys,
is it possible to use dax to do the following?
This file contains the (very simple) table used in the picture above.
What do I aim to do? Basically to dynamically split the values of the “topic” column into how many necessary columns it takes. I've set four cases for this example, but if I have (let's say six) more, would it yet be possible to split the column? If it's not, then only four would already be great. Which formulas should I use to perform this calculation?
In last case if it's not possible to do so by using DAX, then how could it be done with M/Power Query?
Thanks in advace.
Hi Anonymous ,
Please try below measure and add a table visual with these measure and fields
Topic 1 = VAR cur_id = SELECTEDVALUE ( 'Table'[ID] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[ID] = cur_id ) RETURN MAXX ( tmp, [Topic] )Topic 2 = VAR cur_id = SELECTEDVALUE ( 'Table'[ID] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[ID] = cur_id && 'Table'[Topic] <> [Topic 1] ) RETURN MAXX ( tmp, [Topic] )Topic 3 = VAR cur_id = SELECTEDVALUE ( 'Table'[ID] ) VAR cur_asg = SELECTEDVALUE ( 'Table'[Assignee] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[ID] = cur_id && 'Table'[Topic] <> [Topic 1] && 'Table'[Topic] <> [Topic 2] ) VAR _a = MAXX ( tmp, [Topic] ) RETURN IF ( cur_asg = "Pedroso", [Topic 2], _a )Topic 4 = VAR cur_id = SELECTEDVALUE ( 'Table'[ID] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[ID] = cur_id && 'Table'[Topic] <> [Topic 1] && 'Table'[Topic] <> [Topic 2] && 'Table'[Topic] <> 'Table'[Topic 3] ) RETURN MAXX ( tmp, [topic] )Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hi Anonymous ,
Please try below measure and add a table visual with these measure and fields
Topic 1 = VAR cur_id = SELECTEDVALUE ( 'Table'[ID] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[ID] = cur_id ) RETURN MAXX ( tmp, [Topic] )Topic 2 = VAR cur_id = SELECTEDVALUE ( 'Table'[ID] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[ID] = cur_id && 'Table'[Topic] <> [Topic 1] ) RETURN MAXX ( tmp, [Topic] )Topic 3 = VAR cur_id = SELECTEDVALUE ( 'Table'[ID] ) VAR cur_asg = SELECTEDVALUE ( 'Table'[Assignee] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[ID] = cur_id && 'Table'[Topic] <> [Topic 1] && 'Table'[Topic] <> [Topic 2] ) VAR _a = MAXX ( tmp, [Topic] ) RETURN IF ( cur_asg = "Pedroso", [Topic 2], _a )Topic 4 = VAR cur_id = SELECTEDVALUE ( 'Table'[ID] ) VAR tmp = FILTER ( ALL ( 'Table' ), 'Table'[ID] = cur_id && 'Table'[Topic] <> [Topic 1] && 'Table'[Topic] <> [Topic 2] && 'Table'[Topic] <> 'Table'[Topic 3] ) RETURN MAXX ( tmp, [topic] )Please refer the attached .pbix file.
Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
