Creating Matrix of Counts with Master List

I have three tables.

First table is REPORTS_MASTER_LIST containing REPORT_NAME, REPORT_CATEGORY columns. This contains all reports which can be executed by users, and their categories.

Second table is USER_REPORT_EXECUTION containing USERNAME, REPORT_NAME, REPORT_CATEGORY columns. Each row in this table is a report which was executed by a user.

Third table is USER_DEPARTMENTS_LIST containing USERNAME, USER_DEPARTMENT columns. This contains all the users along with their departments.

I want to create a matrix which on the left hand side (rows) shows the REPORT_TYPE, which can be expanded to show REPORT_NAME. On the top of the matrix (columns), it should be the USER_DEPARTMENT. The values shown in the matrix should be the count, representing the number of report executions by USER_DEPARTMENT for each REPORT_NAME.

Currently I am able to do this using just the USER_REPORT_EXECUTION and USER_DEPARTMENT tables. The problem is, if there is a report in the REPORTS_MASTER_LIST which has never been executed by a user, it won't show up in the matrix, although I need it to show up and show '0' for every USER_DEPARTMENT. Is there a way I can do this by integrating the REPORTS_MASTER_LIST table in the matrix somehow?

I used this sample data since you didn't provide any :

Table REPORTS_MASTER_LIST :

REPORT_NAME REPORT_CATEGORY
Report A    Type 1
Report B    Type 1
Report C    Type 2

Table USER_REPORT_EXECUTION :

USERNAME    REPORT_NAME REPORT_CATEGORY
User1       Report A    Type 1
User2       Report A    Type 1
User1       Report B    Type 1

Table USER_DEPARTMENTS_LIST :

USERNAME    USER_DEPARTMENT
User1       Dept 1
User2       Dept 2

I created the following relationships :

• Link REPORTS_MASTER_LIST to USER_REPORT_EXECUTION on REPORT_NAME
• Link USER_REPORT_EXECUTION to USER_DEPARTMENTS_LIST on USERNAME

You need to create a measure to count report executions while including reports with zero counts:

Report Execution Count = 
CALCULATE(
    COUNTROWS(USER_REPORT_EXECUTION),
    USER_REPORT_EXECUTION[REPORT_NAME]
)

Then use COALESCE to display 0 for non executed reports:

Report Execution Count (All Reports) = 
COALESCE(
    CALCULATE(
        COUNTROWS(USER_REPORT_EXECUTION),
        USER_REPORT_EXECUTION[REPORT_NAME] IN VALUES(REPORTS_MASTER_LIST[REPORT_NAME])
    ),
    0
)