Is it possible to unpivot in Power BI using DAX?

Question

I need to create the following chart with DAX, without using Power Query.

This has to be done with the following 2 tables:

Does anyone have an idea how to create this chart without Power Query? You may use calendar tables.

Answer 1

The first proposed solution by Alexis using UNION and SELECTCOLUMNS functions to unpivot a table works very well. Applied to the problem mentioned here, the single formula for a newly created table in Power BI would be:

NewTable = UNION(
   SELECTCOLUMNS('DataTable',"Attribute”,”SJ_Time”,”Value",'DataTable'[Value],"Time",'DataTable'[SJ_Time]),
   SELECTCOLUMNS('DataTable',"Penang_Time”,”Penang_Time”,”Value",'DataTable'[Value],"Time",'DataTable'[Penang_Time]),
   )

Explanation what it does:

The formula creates two tables (A) and (B). The function SELELCTCOLUMNS defines the content of each table:

Table (A) gets a first column called Attribute, which is filled with the first headline's name "SJ_Time" into as many rows as the original 'DataTable' holds. The second column is called Value whose data values are fetched from the column 'DataTable'[Value] of the original table (and will be just repeated in the table (B) later on). The third column is called Time whose data values are fetched from the column 'DataTable'[SJ_Time] of the original table. The latter are the actual data values we want to unpivot.

Table (B) is created in similar fashion, with the difference that the first column is filled with the second headline's name "Penang_Time" and the third column gets its data values from column 'DataTable'[Penang_Time] of the original table - which is the actual unpivoting transaction. The data values of the second column are just repeated.

Finally, the function UNION plunges tables (A) and (B) under one another, so the amount of rows doubles and the unpivoting is complete.

Answer 2

Preface/Rant:

I attempted to answer the question posted at https://community.powerbi.com/t5/Desktop/Using-dax-to-unpivot-a-table/td-p/421116

However because my Microsoft account is neither work or education related I was unable to submit a reply. What a shitty design choice to set up a Q/A forum which has unlimited read access with inhibitively gated write access.

Such being the case, this basically being the same question and me being so pleased with my solution stackoverflow wins this contribution.

You can use your imagination or someone else can modify the solution to better match this specific stackoverflow question. (You would obviously have to take extra steps to tie my proposed id table to a calendar table or something...)

---

Solution to related question:

Given a table like:

"Data Table"
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

Create or add a table like following to the data model:

"Attribute Table"
id      Atribute
1       Penang Time
2       SJ Time

Create a switching iterative aggregation measure called "Time" like the following:

=MINX(
    "Attribute Table",
    SWITCH(
        "Attribute Table"[id],
        1, MIN("Data Table"[Penang Time]),
        2, MIN("Data Table"[SJ Time])
    )
)

(obviously you should switch minx and min for whatever makes the most sense, sumx and sum in my case)

Create a pivot table that has:

Rows                                Values
"Attribute Table"[Attribute]        [Time]
"Data Table"[Value]

And voilà:

"Pivot Table of Justice"
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

 

Answer 3

I don't think there is currently a clean way to unpivot a table using DAX.

However, it is possible to achieve a similar result, but you'll need fairly long and manual code. For example, to unpivot your bottom table, you could write a union like this:

UNION(
  SELECTCOLUMNS(Targets, "Segement", Targets[Segment], "Qtr", "Q1", "Target", Targets[Q1]),
  SELECTCOLUMNS(Targets, "Segement", Targets[Segment], "Qtr", "Q2", "Target", Targets[Q2]),
  SELECTCOLUMNS(Targets, "Segement", Targets[Segment], "Qtr", "Q3", "Target", Targets[Q3]),
  SELECTCOLUMNS(Targets, "Segement", Targets[Segment], "Qtr", "Q4", "Target", Targets[Q4]))