Visual "Matrix" with data in cells

[JellyFish310]

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?

[danextian · Accepted solution]

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.

[v-nmadadi-msft]

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

[v-nmadadi-msft]

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.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[danextian · Accepted solution]

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.

[danextian]

Another approach is to simply concatenate your criteria

Concatenated criteria = 
CONCATENATEX (
    'Table',
    'Table'[Criteria],
    " ",
    CALCULATE ( SELECTEDVALUE ( 'Table'[Criteria] ) )
)

[JellyFish310]

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?

[danextian · Accepted solution]

the index/rank approach will put the criteria into individual cells. there is a pbix attached in my first response.

[BhavinVyas3003]

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. 

[rohit1991]

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.

[JellyFish310]

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)?