Forum Discussion
Grouping in Matrix From Column Names
Apologies if this topic has already been addressed in any previous post.
I have my raw data from SQL View as below....
| SCALE_ID | SCALE_SUB_ID | ENTRY | STATUS | MANTEST_GREEN | MANTEST_RED | MANTEST_AMBER | BOTTEST_GREEN | BOTTEST_RED | BOTTEST_AMBER | BOTTEST_NOTDONE |
| AP_SPEC | SPEC_JPN | 5 | GREEN | 5 | NULL | NULL | 5 | NULL | NULL | NULL |
| AP_SPEC | SPEC_HK | 5 | GREEN | 5 | NULL | NULL | 3 | 2 | NULL | NULL |
| AP_SPEC | SPEC_IN | 1 | AMBER | NULL | NULL | 1 | NULL | NULL | 1 | NULL |
| EU_SPEC | SPEC_FR | 16 | RED | NULL | 16 | NULL | NULL | 9 | 2 | 5 |
| EU_SPEC | SPEC_GB | 1 | GREEN | 1 | NULL | NULL | 1 | NULL | NULL | NULL |
| EU_SPEC | SPEC_LN | 1 | RED | NULL | 1 | NULL | NULL | 1 | NULL | NULL |
| EU_SPEC | SPEC_IR | 3 | RED | NULL | 3 | NULL | NULL | NULL | 2 | 1 |
| EU_SPEC | SPEC_ES | 1 | AMBER | NULL | NULL | 1 | NULL | NULL | 1 | NULL |
I need to transform this in Power BI as below.... I.e. All MANTEST_*** Column Under MANTEST and similarly all BOTTEST_*** Column under BOTTEST. Is there a way in power BI matrix to achive that please?
| SCALE_ID | SCALE_SUB_ID | ENTRY | STATUS | MANTEST | BOTTEST | |||||
| GREEN | RED | AMBER | GREEN | RED | AMBER | NOTDONE | ||||
| AP_SPEC | SPEC_JPN | 5 | GREEN | 5 | NULL | NULL | 5 | NULL | NULL | NULL |
| SPEC_HK | 5 | GREEN | 5 | NULL | NULL | 3 | 2 | NULL | NULL | |
| SPEC_IN | 1 | AMBER | NULL | NULL | 1 | NULL | NULL | 1 | NULL | |
| EU_SPEC | SPEC_FR | 16 | RED | NULL | 16 | NULL | NULL | 9 | 2 | 5 |
| SPEC_GB | 1 | GREEN | 1 | NULL | NULL | 1 | NULL | NULL | NULL | |
| SPEC_LN | 1 | RED | NULL | 1 | NULL | NULL | 1 | NULL | NULL | |
| SPEC_IR | 3 | RED | NULL | 3 | NULL | NULL | NULL | 2 | 1 | |
| SPEC_ES | 1 | AMBER | NULL | NULL | 1 | NULL | NULL | 1 | NULL |
Hi RKM ,
You can create a new table containing "MANTEST" and "BOTTEST", and create a Measure using the Swithch() function to determine the individual values:
Refer to:
Solved: Matrix column header/label custom group - Microsoft Fabric Community
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Apologies again! I realied that the table etc. I pasted here is a little messed up after posting... So let me also share one image...
Hi RKM ,
You can create a new table containing "MANTEST" and "BOTTEST", and create a Measure using the Swithch() function to determine the individual values:
Refer to:
Solved: Matrix column header/label custom group - Microsoft Fabric Community
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
