matrix

How to Make Your Matrix Behave

by

The matrix visual in Power BI is a bit fiddly to work with, particularly when you’re working with text-based data. It likes to auto-summarize any text fields you add, and typically you want a little more control over the header groupings than it wants to give. Here’s an example of what the task data looks like from the measures below:

matrix screenshot

This post is an accompaniment to the YouTube tutorial on how to better control what the matrix visual displays here.

Here’s examples of DAX that will change what’s displayed in the header groupings in a matrix – for example showing blank when expanded but a value when collapsed, vice versa, or show different measures according to the level.

We primarily use ISINSCOPE() along with the field “rows” to selectively display the data.

The DAX

Here’s the DAX used in the tutorial video. 

Matrix - % Completed Effort Days = 
VAR complEffortTask = [% Completed Effort (assignable)]
VAR complEffortProject =
    CALCULATE (
        [% Completed Effort (assignable)],
        ALL ( Tasks ),
        Project[Project ID] = SELECTEDVALUE ( Tasks[Project ID] )
    ) //remove filters on tasks to prevent bookmark visual filters from showing wrong percentage
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( Tasks[Task Name] ), complEffortTask,
        //shows %compl for task if at task level
        ISINSCOPE ( Project[Project Name] ), complEffortProject,
        //shows % compl for project at project level (can add another level for bucket here if using that as a grouping)
        BLANK ()
    )
Matrix - Bucket Name = IF (
    ISINSCOPE ( Tasks[Task Name] ),
    MIN ( Tasks[Sortable Bucket Name] ),
    BLANK ()
)
Matrix - Count Tasks = 
VAR countTasks =
    CALCULATE ( [Count Tasks (assignable only)], REMOVEFILTERS ( Tasks[Overdue?] ) )
RETURN
    IF ( ISINSCOPE ( Tasks[Task Name] ), BLANK (), countTasks )
Matrix - Dynamic URL = //this returns a different link depending on which level you're at in the matrix
VAR projectLink =
    CALCULATE (
        MAX ( Project[Project URL (timeline)] ),
        Project[Project ID] = SELECTEDVALUE ( Tasks[Project ID] )
    )
VAR taskLink =
    SELECTEDVALUE ( Tasks[Task URL] )
RETURN
    SWITCH (
        TRUE (),
        ISINSCOPE ( Tasks[Task Name] ), taskLink,
        ISINSCOPE ( Project[Project Name] ), projectLink,
        BLANK ()
    )
Matrix - Last Modified = SWITCH (
    TRUE (),
    ISINSCOPE ( Tasks[Task Name] ), MAX ( Tasks[Modified On] ),
    ISINSCOPE ( Project[Project Name] ), CALCULATE ( MAX ( Tasks[Modified On] ), ALL ( Tasks ), KEEPFILTERS ( Project ) ),
    BLANK ()
)
Matrix - Overdue Tasks = IF (
    ISINSCOPE ( Tasks[Task Name] ),
    BLANK (),
    [Count Overdue Tasks (assignable)]
)
Matrix - Project Manager = SWITCH (
    TRUE (),
    ISINSCOPE ( Tasks[Task Name] ), MIN ( Tasks[Assignees] ),
    ISINSCOPE ( Project[Project Name] ), CALCULATE ( MIN ( Project[Project Manager] ) ),
    "some other text"
)

The demo uses the dataset created in this tutorial on modeling premium Planner data, but the concepts also work for the twin for standard data (though the data structure is slightly different – there’s no effort hours, for example).

Leave a Comment