Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi,
I have a table as below:
Org1 | Org2 | Dept1 | Dept2 |
O1 | O2 | Dept1.1 | Dept2.1 |
O1 | O3 | Dept1.2 | Dept2.2 |
O2 | Dept1.3 | Dept2.3 | |
O1 | O2 | Dept1.4 | Dept2.4 |
O1 | Dept1.5 | Dept2.5 |
I need to create a hierarchy table as below to create a matrix graph in power bi.
L1 | L2 | L3 |
O1 | Dept1 | Dept1.1 |
O1 | Dept1 | Dept1.2 |
O1 | Dept1 | Dept1.4 |
O1 | Dept1 | Dept1.5 |
O1 | Dept2 | Dept2.1 |
O1 | Dept2 | Dept2.2 |
O1 | Dept2 | Dept2.4 |
O1 | Dept2 | Dept2.5 |
O2 | Dept1 | Dept1.1 |
O2 | Dept1 | Dept1.3 |
O2 | Dept1 | Dept1.4 |
O2 | Dept2 | Dept2.1 |
O2 | Dept2 | Dept2.3 |
O2 | Dept2 | Dept2.4 |
O3 | Dept1 | Dept1.2 |
O3 | Dept2 | Dept2.2 |
Any quick help would be appreciated.
Solved! Go to Solution.
Hi @Anonymous
If you can transform your data in power query editor, you can try to duplicate you data table ,then use append and unpivot function to achieve your goal. Or you can build a calculated table by dax.
Dax:
Dax =
VAR _O1 =
SUMMARIZE (
FILTER ( 'Data Table', 'Data Table'[Org1] <> BLANK () ),
'Data Table'[Org1],
'Data Table'[Dept1],
'Data Table'[Dept2]
)
VAR _O2 =
SUMMARIZE (
FILTER ( 'Data Table', 'Data Table'[Org2] <> BLANK () ),
'Data Table'[Org2],
'Data Table'[Dept1],
'Data Table'[Dept2]
)
RETURN
VAR _T =
UNION ( _O1, _O2 )
RETURN
UNION (
SUMMARIZE ( _T, [Org1], [Dept1], "L2", "Dept1" ),
SUMMARIZE ( _T, [Org1], [Dept2], "L2", "Dept2" )
)
Result is as below.
Power Query Editor:
Duplicate Data Table twice and rename them as O1(Remove Org2 in this table)/O2 (Remove Org1 in this table)
Append them as a new table named Append1.
Unpivot Dept1 and Dept2, rename the columns as L1,L2,L3, and sort the columns by ascending.
Result is as below.
You can download the pbix file from this link: Dax to make a table ready for matrix graphs in power bi
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
If you can transform your data in power query editor, you can try to duplicate you data table ,then use append and unpivot function to achieve your goal. Or you can build a calculated table by dax.
Dax:
Dax =
VAR _O1 =
SUMMARIZE (
FILTER ( 'Data Table', 'Data Table'[Org1] <> BLANK () ),
'Data Table'[Org1],
'Data Table'[Dept1],
'Data Table'[Dept2]
)
VAR _O2 =
SUMMARIZE (
FILTER ( 'Data Table', 'Data Table'[Org2] <> BLANK () ),
'Data Table'[Org2],
'Data Table'[Dept1],
'Data Table'[Dept2]
)
RETURN
VAR _T =
UNION ( _O1, _O2 )
RETURN
UNION (
SUMMARIZE ( _T, [Org1], [Dept1], "L2", "Dept1" ),
SUMMARIZE ( _T, [Org1], [Dept2], "L2", "Dept2" )
)
Result is as below.
Power Query Editor:
Duplicate Data Table twice and rename them as O1(Remove Org2 in this table)/O2 (Remove Org1 in this table)
Append them as a new table named Append1.
Unpivot Dept1 and Dept2, rename the columns as L1,L2,L3, and sort the columns by ascending.
Result is as below.
You can download the pbix file from this link: Dax to make a table ready for matrix graphs in power bi
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |