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

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

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