Forum Discussion
Transposing / Transforming data with DAX
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!!
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] ) )
5 Replies
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 _ASSETOutput:
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/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?
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] ) )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?
You can wrap a FILTER around the whole thing.
FILTER ( UNION ( [...] ), NOT ISBLANK ( [Asset] ) )
