Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

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 f...
Need Help
lbendlin's avatar
lbendlin
Super User
3 years ago

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's avatar
lbendlin
Super User
3 years ago

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"
                )
        )
    )
)