Forum Discussion
matrix visual with calculation group
Hi GQ00
This is a limitation of the matrix visual itself. The upper hierarchy levels act as column headers because they come from dimensions, and in a matrix, measures are always placed at the lowest level of the hierarchy.
A workaround is to materialize the measure results into a physical column using a disconnected table. You can reuse the field parameter table as that disconnected table, but it won’t behave as originally intended — it will mainly act as a placeholder for the measure values rather than a true parameter.
Please see the attached pbix.
a — The numbers must match, since they are the basin in conditionally returning the measures.
b — That format string would only work if it were plain text rather than a DAX expression. DAX does not provide a function that can evaluate a DAX expression stored as text. Therefore, a separate conditional measure must be created and used as the dynamic format string. For the percentage variance, the format string can be changed directly within the calculation item settings.
Hi,
Thank you both for your help and patience.
danextian the pbix you attached was incredibly helpful. By copying the dax in there - without fully understanding it - I manage to reproduce the same matrix with the intended order. The fact that most columns populate automatically is highly appreciated, thanks for going the extra mile.
The field parameters measure is used as values in the matrix. The columns are the actual field name and the previous calculation group for time comparison. amazing
However I have two follow-up questions.
a) if I need to add/modify measures in the future. Do I simply adjust the "param" variable in the calculated table and the field parameter measure? Both need to have the same orders right, since they are not linked but share the headers in the matrix
b)The format string didn’t populate correctly, likely because I’m using a dynamic format. Do you have any workaround for this? I’m fine with defining it manually if needed.
Thanks so much, I'm surprised at the complexity of sorting columns in a matrix, but I guess there are technical explanations for this behaviour beyond my comprehension.
a — The numbers must match, since they are the basin in conditionally returning the measures.
b — That format string would only work if it were plain text rather than a DAX expression. DAX does not provide a function that can evaluate a DAX expression stored as text. Therefore, a separate conditional measure must be created and used as the dynamic format string. For the percentage variance, the format string can be changed directly within the calculation item settings.
Hi danextian, thanks for your continuous support, you are clearly very knowledgeable and have managed to not only produce an explanation but also a sample PBIX with dax code in it, making this a great solution to bypass the current limitation of CG in matrix.
Your thoroughness and patience are very much appreciated!