Turning columns into rows in DAX

[IssieWissie]

Hi everyone,

I'm sure this is a well known topic and I'm aware this is much easier done in Power Query. The issue is that the data I am attempting to pivot is obtained from calculated columns in DAX and I'm not sure if it'd be possible to get this into Power Query.

My data looks as follows:

Date 1	Date 2	Date 3	Hours
05/06/2023	12/06/2023	19/06/2023	77
05/06/2023	12/06/2023		51.2

The solution I am looking for looks like:

Date	Hours
05/06/2023	77
12/06/2023	77
19/06/2023	77
05/06/2023	51.2
12/06/2023	51.2

or

Date	Hours
05/06/2023	128.2
12/06/2023	128.2
19/06/2023	77

Is it possible to achieve this in DAX?

[v-xinruzhu-msft · Accepted solution]

Hi @IssieWissie 

You can refer to the following code

Table 2 =
VAR a =
    SUMMARIZE ( FILTER ( 'Table', [Date 1 ] <> BLANK () ), [Date 1 ], [Hours ] )
VAR b =
    SUMMARIZE (
        FILTER ( 'Table', [Date 2] <> BLANK () ),
        'Table'[Date 2],
        'Table'[Hours ]
    )
VAR c =
    SUMMARIZE (
        FILTER ( 'Table', [Date 3] <> BLANK () ),
        [Date 3],
        'Table'[Hours ]
    )
RETURN
    UNION ( a, b, c )

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.