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 :
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
)