Pivot/Unpivot calculated columns using DAX

[seanmcc]

Hi Guys,

I am looking for some assistance in pivoting and unpivoting a table without using power query.

I have done some research and found that I could possibly use UNION and SUMMARIZECOLUMNS funtions, however I am struggling to get my head around how to pivot and unpivot within same table. I am unsure if this is possible.

I have a table in desktop where I have a number of calculated columns ( for example, 5 min relative and 10 min relative).

Current Table

Team

Season

Competition

Game

Venue

Fixture

Date

Result

Goals For

Goals Against

Position Group

Position

Player

Attribute

5 min Relative

10 min Relative

Manchester United

2023/24

Premier League

Game 1

Home

Manchester City (H)

01/09/2022

Win

1

0

Defender

CB

Harry Maguire

Total Distance

106.2

99.5

Manchester United

2023/24

Premier League

Game 1

Home

Manchester City (H)

01/09/2022

Win

1

0

Defender

CB

Harry Maguire

Sprint Distance

2.4

1.9

What I am trying to do from the original table is to pivot 'Attributes' into columns and unpivot the columns '5 min relative' and '10 min relative' into a column called 'Time Period'. See below for the desired outcome.

Desired Table

Team

Season

Competition

Game

Venue

Fixture

Date

Result

Goals For

Goals Against

Position Group

Position

Player

Time Period

Total Distance

Sprint Distance

Manchester United

2023/24

Premier League

Game 1

Home

Manchester City (H)

01/09/2022

Win

1

0

Defender

CB

Harry Maguire

5 min Relative

106.2

2.4

Manchester United

2023/24

Premier League

Game 1

Home

Manchester City (H)

01/09/2022

Win

1

0

Defender

CB

Harry Maguire

10 min Relative

99.5

1.9

If there is a way to do this with DAX, it would be great. Otherwise, would there be another way of acheiving the desired outcome?

Thank you in advance.

Sean

[seanmcc]

Hi @lbendlin, yes the attribute field names are always the same and present. The columns '5 min Relative', '10 min Relative' etc will always be present, however not always have values within them.

I hope that helps.

Kind Regards,

Sean

[lbendlin]

This DAX is based on the sample data you provided.  The CALCULATE filters will need to be adjusted for scenarios with more than one player etc.

Table2 =
UNION (
    SUMMARIZE (
        'Table',
        [Team],
        [Season],
        [Competition],
        [Venue],
        [Fixture],
        [Date],
        [Result],
        [Goals For],
        [Goals Against],
        [Position Group],
        [Position],
        'Table'[Player],
        "Time Period", "5 min Relative",
        "Total Distance",
            CALCULATE (
                MAX ( 'Table'[5 min Relative] ),
                'Table'[Attribute] = "Total Distance"
            ),
        "Sprint Distance",
            CALCULATE (
                MAX ( 'Table'[5 min Relative] ),
                'Table'[Attribute] = "Sprint Distance"
            )
    ),
    SUMMARIZE (
        'Table',
        [Team],
        [Season],
        [Competition],
        [Venue],
        [Fixture],
        [Date],
        [Result],
        [Goals For],
        [Goals Against],
        [Position Group],
        [Position],
        'Table'[Player],
        "Time Period", "10 min Relative",
        "Total Distance",
            CALCULATE (
                MAX ( 'Table'[10 min Relative] ),
                'Table'[Attribute] = "Total Distance"
            ),
        "Sprint Distance",
            CALCULATE (
                MAX ( 'Table'[10 min Relative] ),
                'Table'[Attribute] = "Sprint Distance"
            )
    )
)

[lbendlin]

Here's another, slightly more concise way:

Table2 =
CROSSJOIN (
    SUMMARIZE (
        'Table',
        [Team],
        [Season],
        [Competition],
        [Venue],
        [Fixture],
        [Date],
        [Result],
        [Goals For],
        [Goals Against],
        [Position Group],
        [Position],
        'Table'[Player]
    ),
    UNION (
        ROW (
            "Time Period", "5 min Relative",
            "Total Distance",
                CALCULATE (
                    MAX ( 'Table'[5 min Relative] ),
                    'Table'[Attribute] = "Total Distance"
                ),
            "Sprint Distance",
                CALCULATE (
                    MAX ( 'Table'[5 min Relative] ),
                    'Table'[Attribute] = "Sprint Distance"
                )
        ),
        ROW (
            "Time Period", "10 min Relative",
            "Total Distance",
                CALCULATE (
                    MAX ( 'Table'[10 min Relative] ),
                    'Table'[Attribute] = "Total Distance"
                ),
            "Sprint Distance",
                CALCULATE (
                    MAX ( 'Table'[10 min Relative] ),
                    'Table'[Attribute] = "Sprint Distance"
                )
        )
    )
)

[lbendlin]

Do you need this to be dynamic or do you know that the attribute and time period field names/values are always the same and always present?