powerbidaxpowerbi-desktoppowerbi-datasource

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?


Solution

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

    enter image description here