Column and Row Grouping

[Charuta]

UniqID	keyID	Source	LinkType	Destination
1	101	A	blocks	C
2	101	A	is blocked by	B
3	101	A	depends	D
4	102	B	is blocked by	D
5	102	B	blocks	A
6	103	C	is blocked by	A
7	104	D	blocks	B
8	104	D	is depended by	E

I have data in the above format in my database 

What I need to show is something like below so that I can use it further as columns in decomposition tree - Is this feasible?

Source	LinkType 1	Destination	LinkType 2	Key	LinkType 3	Key
A	blocks	C
A	is blocked by	B	is blocked by	D
A	depends	D
C	is blocked by	A	is blocked by	B	is blocked by	D
D	blocks	B	blocks	A
D	is depended by	E

[v-yanjiang-msft · Accepted solution]

Hi @Charuta ,

According to your description, here's my solution.

Create four measures.

Key2 =
MAXX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Source] = MAX ( 'Table'[Destination] )
            && 'Table'[LinkType] = MAX ( 'Table'[LinkType] )
    ),
    'Table'[Destination]
)

Link Type2 = IF([Key2]=BLANK(),BLANK(),MAX('Table'[LinkType]))

Key3 =
MINX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Source]
            = MAXX (
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Source] = MAX ( 'Table'[Destination] )
                        && 'Table'[LinkType] = MAX ( 'Table'[LinkType] )
                ),
                'Table'[Destination]
            )
            && 'Table'[LinkType] = MAX ( 'Table'[LinkType] )
    ),
    'Table'[Destination]
)

Link Type3 = IF([Key3]=BLANK(),BLANK(),MAX('Table'[LinkType]))

 Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.