Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!!
Solved! Go to 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]
)
)
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] )
)
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/
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
15 | |
10 |
User | Count |
---|---|
57 | |
50 | |
44 | |
21 | |
19 |