Matrix with multiple dynamic elements in each cell

[Kazu]

Here is some sample data and visuals I would like to create. The output is essentially a matrix of Business Unit x Phase. Each intersection or cell shows all Projects of the correspoinding BU x Phase. Some additional requirements:

1) Project names are color coded by Category.

2) can be filtered by category so it is shown like the bottom image when filtered to Category 2 (see the position of Project B)

3) project name will be a hyperlink to a relevant project page  (part of source data now shown here)

I think if we ignore the requirement 3, I can create a measure that CONCATENATEX project names and create an SVG image to show project names color coded. However, I don't think we can specify multiple hyperlinks in a single SVG image in a matrix cell.

Another approach would be to create a calculated column for index per BU and Phase, using RANKX(). This approach ensures there is only one element in a cell, and would work fine except for the requirement 2). Because the index is a row dimension and static, Project B cannot move up to the index 1 position when Project A is filtered out.

So now I am considering yet another approach and got stuck. The idea is to create a text measure that returns a name of a project for the BU, Phase and Index in the context. Index is not like a rank in the above but rather an independent dimension or entity, so the project name for BusinessUnit: BU1 x Phase:Design x Index:1 would return either Project A or Project B depending on the context. I guess it is doable by INDEX() function, but has not been successful so far.

I think the the last/third approach above makes the most sense but am open to other approches. Anybody has any suggestions?

[Anonymous]

Hi @Kazu ,

Please try below steps:

1. create a measure with below dax formula

Measure =
VAR _a =
    SELECTEDVALUE ( 'Table'[Business Unit] )
VAR _b =
    SELECTEDVALUE ( 'Table'[Phase] )
VAR _C =
    VALUES ( 'Table'[Category] )
VAR tmp =
    CALCULATETABLE (
        VALUES ( 'Table'[Project] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Business Unit] = _a
                && 'Table'[Phase] = _b
                && 'Table'[Category] IN _C
        )
    )
VAR ctn =
    COUNTROWS ( tmp )
VAR _str =
    CONCATENATEX ( tmp, [Project], "
" )
RETURN
    _str

2. add a slicer and matrix visual

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Kazu]

@Anonymous , thank you for your response. 

I think the measure you suggested only meets my requirement #2 but not 1 and 3. Also, I am not sure if all your "VAR" steps are needed. Can't we simply do like this?