Setting up a Matrix

[dataunknown]

I currently have a dataset that is imported from a SharePoint list that looks like this:

Primary	Secondary	Signatory	Status
Tom	Sam	Mary	In Process
Joe	Tom	Kate	On Hold
Jeff	Paul	Mary	In Process
Paul	Brad	Kate	On Hold
Ed	Jeff	Kate	In Process

And I want the matrix to look like this:

Name	In Process	On Hold	Signatory
Tom	1	1	0
Joe	1	0	0
Jeff	1	1	0
Paul	1	1	0
Ed	1	0	0
Sam	0	1	0
Brad	0	1	0
Mary	0	0	2
Kate	0	0	3

Any suggestions would be greatly appreciated!

[gmsamborn · Accepted solution]

Hi @dataunknown 

I created an Employee dimension table in Power Query and then used the following measures.

In Process = 
    COUNTROWS(
        FILTER(
            ALL( 'Table' ),
            'Table'[Status] = "In Process"
                && ( 'Table'[Primary] = SELECTEDVALUE( 'Employee'[Employee] )
                    || 'Table'[Secondary] = SELECTEDVALUE( 'Employee'[Employee] )
                )
        )
    )

On Hold = 
    COUNTROWS(
        FILTER(
            ALL( 'Table' ),
            'Table'[Status] = "On Hold"
                && ( 'Table'[Primary] = SELECTEDVALUE( 'Employee'[Employee] )
                    || 'Table'[Secondary] = SELECTEDVALUE( 'Employee'[Employee] )
                )
        )
    )

Signatory = 
    CALCULATE(
        COUNTROWS( 'Table' ),
        USERELATIONSHIP( 'Employee'[Employee], 'Table'[Signatory] )
    )

I hope this helps.

dataunknown.pbix

[Idrissshatila]

Hello @dataunknown ,

check this out https://youtu.be/hGj2axffxHo?si=uWEBXL7Hu0C3mhaa