all combination of a column

[Anonymous]

Hello all,

Recently I am trying to solve this problem but could not way to find it, I decided to post it here as my first post. I hope there is a way to do this in PowerBi.

I have a table with two columns that show the Group name and email address.

I want to reshape this data to use it in a chord visual. For that visual, I need a new table with three-column "to, from, the value".

I would like to see something like this.

 

To and From column will be all combinations of Group names and the Value column will be the number of common people across Groups.

Thank you and best regards

[Anonymous]

To get all possibilities you can import the "to" column as a table and import the "from" column as a second table. Then merge to get all possible combinations. 

[mahoneypat · Accepted solution]

One way to do that is as follows

Make a crossjoin table to get all the combinations of Group Name with

CJ =
CROSSJOIN (
    SELECTCOLUMNS (
        'Group',
        "To", 'Group'[Group Name]
    ),
    SELECTCOLUMNS (
        'Group',
        "From", 'Group'[Group Name]
    )
)

Then make a table visual with the From and To columns from that table, and use this measure, replace Group with your actual initial table name.

Count From To =
VAR vFrom =
    SELECTEDVALUE ( CJ[From] )
VAR vTo =
    SELECTEDVALUE ( CJ[To] )
RETURN
    COUNTROWS (
        FILTER (
            VALUES ( 'Group'[Email Address] ),
            CALCULATE (
                DISTINCTCOUNT ( 'Group'[Group Name] ),
                'Group'[Group Name] = vFrom
                    || 'Group'[Group Name] = vTo
            ) = 2
        )
    )

Pat

[Anonymous]

Thank you, Pat!  This is exactly what I wanted to have!