Forum Discussion

A_a_a's avatar
A_a_a
Helper III
3 years ago
Solved

Moving Columns Headers to Rows

Hi All!   I have some columns, which show minutes based on calculation time minus time (end time - start time).     I want to create matrix table showing total minutes during the hours by ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    A_a_a Well, perhaps you could move the calculations to PQ and then unpivot. Otherwise, you could potentially use this: DAX Unpivot - Microsoft Power BI Community. Last thought, create a disconnected table that just lists your times like:

    Time

    0:00

    1:00

    2:00

     

    Use this for your rows. Create a measure like this:

    Measure =
  VAR __Time = MAX('DisconnectedTable'[Time])
  VAR __Result = 
    SWITCH(__Time,
      "0:00", SUM('Table'[0:00]),
      "1:00", SUM('Table'[1:00]),
      "2:00", SUM('Table'[2:00]),
      ...
    )
Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

A_a_a In Power Query Editor, try unpivoting your time columns.

A_a_a's avatar
A_a_a
Helper III
3 years ago

Hi Greg_Deckler 

 

Thank you for your message.

I don't think I can unpivot them because they are not in power query editor, the columns are added in Data View.

 

What do you think?

 

Thanks,

G.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    A_a_a Well, perhaps you could move the calculations to PQ and then unpivot. Otherwise, you could potentially use this: DAX Unpivot - Microsoft Power BI Community. Last thought, create a disconnected table that just lists your times like:

    Time

    0:00

    1:00

    2:00

     

    Use this for your rows. Create a measure like this:

    Measure =
  VAR __Time = MAX('DisconnectedTable'[Time])
  VAR __Result = 
    SWITCH(__Time,
      "0:00", SUM('Table'[0:00]),
      "1:00", SUM('Table'[1:00]),
      "2:00", SUM('Table'[2:00]),
      ...
    )