Forum Discussion
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
- AnonymousNot 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 _str2. 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.- KazuHelper 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?