Using dax to unpivot a table

[rawmeat]

Hi all,

I need to unpivot a table. The challenge here is we are using ssas tabular model so I can not just select the target columns and unpivot under "edit query" mode.

The table is like the following:

Value   SJ Time    Penang Time

60         4/18            4/19

50         4/15            4/16

20         4/14            4/15

20         4/15            4/15

10         4/13            4/14 

10        4/20             4/21

The result should look like the following:

Attribute          Value         Time

Penang Time    10             4/14

Penang Time    10             4/21

Penang Time    20             4/15

Penang Time    50             4/16

Penang Time    60             4/19

SJ Time             10            4/13

SJ Time             10            4/20

SJ Time             20            4/14

SJ Time             20            4/15

SJ Time             50            4/15

SJ Time             60            4/18

Thanks much

Jason

[Anonymous · Accepted solution]

Same issue I have.

Somce one suggested UNION function for a question on another website - StackOverFlow

https://stackoverflow.com/questions/50213905/is-it-possible-to-unpivot-in-power-bi-using-dax

See, If this helps 🙂

[v-yuta-msft]

Hi rawmeat,

Are you using live connection mode or import mode? If you are using live connection mode, you can't do changed in your data model so that the "Unpivot Column" is grey and you should do Unpivot operation in your SSAS model. If you are using import mode, you can Unpivot your column by clicking Transform-> Unpivot Column.

Hope it's helpful for you.

Regards,

Jimmy Tao

[rawmeat]

Yes the issue here is I am using ssas direct query mode so I can't use edit query option in power bi desktop.

[ChrisMendoza]

hello @rawmeat,

In the query editor > Transform there is a clickable button 'Unpivot Columns'.

[ChrisMendoza]

I see I replied hastily in my last few moments before I got off work on Friday. Forgive me.

I am curious to know why having a ssas tabular model has such a limitation. Any explanation or reading that someone could provide would be appreciated.