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
Anonymous's avatar
Anonymous
Not applicable
3 years ago

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