Split rows into several columns

[Pedro503]

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.

[v-binbinyu-msft · Accepted solution]

Hi @Pedro503 ,

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.

[Pedro503]

Thank you very much for your reply - and sorry for my late response.

Kudos to you.

[Smalfly]

Hi @Pedro503 ,

I don't know how to do that in DAX but you can use Pivot column to do it using M query.

Please check this page for an example on how it works.

[nazrinf]

Hello,

Maybe you can try using Matrix table to get the result. Put the Topic in Column and Value field. It will takes the first value of Topic.

Thanks.

[Pedro503]

Thanks for your reply, nazrinf.

What I'd like to do is to actually transform the table structure. I've tried doing so using summarize and addcollumns, but I got nothing.

Do you know whether it's possible?

Thanks in advance