Dax to make a table ready for matrix graphs in power bi

[Anonymous]

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.

[Anonymous · Accepted 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.