Forum Discussion
Solved
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 B...
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] ) )
VahidDM
Super User
Super UserHi 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/
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?
