Transposing / Transforming data with DAX

[mariajuliao]

I want to transform the data from this format:

ID	CaseNumber	Asset1	Asset2
AA	BB	1	2
CC	DD	3	4

To this format:

ID	CaseNumber	Asset	Value
AA	BB	Asset1	1
AA	BB	Asset2	2
CC	DD	Asset1	3
CC	DD	Asset2	4

Since columns Asset1, Asset2, Asset3, etc. are calculated column in DAX I cannot use PowerQuery to unpivot the data. Let me know any solutions with DAX. 

Thanks!!

[AlexisOlson · Accepted solution]

It's a bit manual, but you can do it as I demonstrated here:

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

UNION (
    SELECTCOLUMNS (
        T,
        "ID",         T[ID],
        "CaseNumber", T[CaseNumber],
        "Asset",      "Asset1",
        "Value",      T[Asset1]
    ),
    SELECTCOLUMNS (
        T,
        "ID",         T[ID],
        "CaseNumber", T[CaseNumber],
        "Asset",      "Asset2",
        "Value",      T[Asset2]
    )
)

[mariajuliao]

This was very helpful!!

One other question -- Some of the values under the Asset1, Asset2 columns are blank. Is there any way that I can ommit those? 

[AlexisOlson]

You can wrap a FILTER around the whole thing.

FILTER ( 
    UNION ( 
        [...]
    ),
    NOT ISBLANK ( [Asset] )
)

[VahidDM]

Hi @mariajuliao 

Try this code to create a new table with DAX:

Table 2 =
VAR _A =
    SUMMARIZE ( 'Table', 'Table'[ID], 'Table'[CaseNumber] )
VAR _B =
    SELECTCOLUMNS ( { "Asset1", "Asset2" }, "Asset", [Value] )
VAR _C =
    CROSSJOIN ( _A, _B )
VAR _ASSET =
    ADDCOLUMNS (
        _C,
        "Value",
            IF (
                [Asset] = "Asset1",
                CALCULATE (
                    MAX ( 'Table'[Asset1] ),
                    FILTER (
                        'Table',
                        'Table'[ID] = EARLIER ( [ID] )
                            && 'Table'[CaseNumber] = EARLIER ( [CaseNumber] )
                    )
                ),
                CALCULATE (
                    MAX ( 'Table'[Asset2] ),
                    FILTER (
                        'Table',
                        'Table'[ID] = EARLIER ( [ID] )
                            && 'Table'[CaseNumber] = EARLIER ( [CaseNumber] )
                    )
                )
            )
    )
RETURN
    _ASSET

Output:

Sample file attached.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[mariajuliao]

This was very helpful!!

One other question -- Some of the values under the Asset1, Asset2 columns are blank. Is there any way that I can ommit those?