Forum Discussion
Matrix Visualisation
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.
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
3 Replies
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
Thanks Owen. What if the Job# is already in numeric but not in sequence ? Do I still need to create index column ?
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:
