Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
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
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"
)
)
)
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"
)
)
)
)
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?
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
79 | |
63 | |
52 | |
47 |
User | Count |
---|---|
215 | |
89 | |
76 | |
66 | |
60 |