Matrix Visualisation

[Vincent_wong]

Hi, newbie here on Power BI. I'm trying to create a task list grouped by week and with conditional formatting based on status.

1. concatenate Job#, Job Description, Owner with different lines.

2. Grouped by week

3. Show status with colour coding

i tried using Matrix Visualisation (not sure if this is the right visuals to go with). I can perform step 1. When it comes to step 2, i couldn't list out all tasks (only first or last records). I have attached a picture for the desired output. Thanks for your help.

[OwenAuger]

You're welcome 🙂
Actually, the purpose of the Index column is to act as a row index of the Matrix visual. So it is required regardless of the type of the Job # column.

Within a given filter context (a particular Week in this case), Index = 1 corresponds to the 1st Job #, Index = 2 corresponds to the 2nd Job #, etc.

I actually hid the Index row labels in the Matrix visual (by narrowing to zero width). Here it is unhidden:

[OwenAuger · Accepted solution]

Hi @Vincent_wong 

Yes, you can do something like that. I have attached a small example using your data.

1. I recommend adding a column in Power Query containing the concatenation of columns delimited by line feeds:

2. Create an Index table containing a single column Index, which contains indexes from 1 up to the maximum number of Jobs that you might need to display in one column.

3. Create measures as follows:

Job Label by Index = 
VAR CurrentIndex = SELECTEDVALUE ( Index[Index] )
VAR JobSummary =
    SUMMARIZE ( Jobs, Jobs[Job #], Jobs[Job Label] )
RETURN
    SELECTCOLUMNS (
        INDEX ( CurrentIndex, JobSummary, ORDERBY ( Jobs[Job #] ) ),
        Jobs[Job Label]
    )

Job Label Colour = 
VAR CurrentIndex = SELECTEDVALUE ( Index[Index] )
VAR JobSummary = SUMMARIZE ( Jobs, Jobs[Job #], Jobs[Status] )
VAR JobStatus =
    SELECTCOLUMNS (
        INDEX ( CurrentIndex, JobSummary, ORDERBY ( Jobs[Job #] ) ),
        Jobs[Status]
    )
RETURN
    SWITCH (
        JobStatus,
        "Done", "#92D050", -- green
        "In Progress", "#FFFF00" -- yellow
    )

3. Construct Matrix visual as follows, with Background Color conditional formatting using the field Job Label Colour:

4. I didn't worry about the legend, but you can create that separately.

Does something like this work for you?

Regards

[Vincent_wong]

Thanks Owen. What if the Job# is already in numeric but not in sequence ? Do I still need to create index column ?