cancel
Showing results for
Did you mean:

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.

Helper I

## Pivot/Unpivot calculated columns using DAX

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?

Sean

4 REPLIES 4
Helper I

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

Super User

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"
)
)
)
``````
Super User

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"
)
)
)
)``````
Super User

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?

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors