Forum Discussion

Kazu's avatar
Kazu
Helper II
2 years ago

Matrix with multiple dynamic elements in each cell

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?

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      Kazu
      Helper II

      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?