Forum Discussion
Visual "Matrix" with data in cells
Looking for some help. I have data that looks like this:
| Group | Org | Criteria | Status |
| Group 1 | Team 1 | 101 | R |
| Group 1 | Team 1 | 102 | Y |
| Group 1 | Team 2 | 103 | G |
| Group 1 | Team 3 | 104 | G |
| Group 2 | Team 4 | 105 | G |
| Group 2 | Team 4 | 106 | Y |
| Group 2 | Team 4 | 107 | R |
| Group 2 | Team 4 | 108 | G |
| Group 2 | Team 5 | 109 | R |
| Group 2 | Team 5 | 110 | C |
| Group 3 | Team 6 | 111 | Y |
| Group 3 | Team 7 | 112 | R |
| Group 3 | Team 8 | 113 | G |
With that data i'm trying to make a visual that looks like a "matrix" but shows different number of "columns" - the number of Criteria for each Group/Team combination. Something like this:
| Group | Org | ||||
| Group 1 | Team 1 | 101 | 102 | ||
| Group 1 | Team 2 | 103 | |||
| Group 1 | Team 3 | 104 | |||
| Group 2 | Team 4 | 105 | 106 | 107 | 108 |
| Group 2 | Team 5 | 109 | 110 | ||
| Group 3 | Team 6 | 111 | |||
| Group 3 | Team 7 | 112 | |||
| Group 3 | Team 8 | 113 |
In this case, the full columns are irelevent, but there are "different" numbers of "columns" for each row depending on the number of Criteira for each Team/Group combination.
Also, the indivial cells (the Criteria Numbers) should be conditional formatted based on the Status value.
Anyone have any thoughts on how to accomplish this in one visual, which could be filtered using a slicer?
Hi JellyFish310
Add a column that ranks your criteria within their own group and org. Add another column to repeat an invisible character based on those rank numbers - this will be used as the column header.
Index Within Group Org = RANKX ( FILTER ( 'Table', 'Table'[Group] = EARLIER ( 'Table'[Group] ) && 'Table'[Org] = EARLIER ( 'Table'[Org] ) ), 'Table'[Criteria], , ASC, DENSE ) ------------------ Invisible Index = REPT ( UNICHAR ( 8203 ), 'Table'[Index Within Group Org] )Please see the attached pbix.
the index/rank approach will put the criteria into individual cells. there is a pbix attached in my first response.
10 Replies
Hii JellyFish310
Power BI does not support a matrix with a variable number of columns per row.The recommended approach is to keep the data in long format and use a Table visual with a DAX measure that concatenates criteria horizontally per Group/Team. Conditional formatting can be embedded in the measure (or via SVG) to reflect status. This solution works with slicers and avoids unsupported matrix behavior.
Thanks, that's what I was expecting.
I know how to concatenate the data in a measure, do have any resources on how to conditional format within that measure (the non-SVG way)?
For matrix visuals, The column structure is generaly fixed.
The other way to handle this is by creating a dynamic index or rank for Criteria within each Group and Team. This index should update automatically whenever the data refreshes.You then use this index as the matrix column.
Hi JellyFish310
Add a column that ranks your criteria within their own group and org. Add another column to repeat an invisible character based on those rank numbers - this will be used as the column header.
Index Within Group Org = RANKX ( FILTER ( 'Table', 'Table'[Group] = EARLIER ( 'Table'[Group] ) && 'Table'[Org] = EARLIER ( 'Table'[Org] ) ), 'Table'[Criteria], , ASC, DENSE ) ------------------ Invisible Index = REPT ( UNICHAR ( 8203 ), 'Table'[Index Within Group Org] )Please see the attached pbix.
Another approach is to simply concatenate your criteria
Concatenated criteria = CONCATENATEX ( 'Table', 'Table'[Criteria], " ", CALCULATE ( SELECTEDVALUE ( 'Table'[Criteria] ) ) )Thanks for your response. I understand how to do a simple concatenation to list the "criteria numbers" in one measure.
The kicker is that i absolutely need the conditional formatting (preferably the background) based on the "Criteria Status."
I doubt with the concat, or rank, method this is possible?
Hi,
PBI file attached.
Hope this helps.
Hi JellyFish310
I wanted to check if you had the opportunity to review the information provided by danextian , Ashish_Mathur . Please feel free to contact us if you have any further questions.
Thank you.Hi JellyFish310
May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank you
