Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Vincent_wong
Regular Visitor

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.

 

IMG_0005.jpeg

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

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:

OwenAuger_0-1697370155096.png

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:

OwenAuger_2-1697370310186.png

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

 

Does something like this work for you?

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

View solution in original post

3 REPLIES 3
OwenAuger
Super User
Super User

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_0-1697408711818.png

 


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn
OwenAuger
Super User
Super User

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:

OwenAuger_0-1697370155096.png

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:

OwenAuger_2-1697370310186.png

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

 

Does something like this work for you?

 

Regards


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
Twitter
LinkedIn

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors