Forum Discussion
PBI Matrix
Hi,
I have a database table with the below fields:
| ID Field |
| Delay Reason 1 |
| Delay Reason 2 |
| Delay Reason 3 |
Delay Reason 1, 2 and 3 fields are like flag fields - i.e. for every ID Field, there could be a value in the only one of the Delay Reason fields like below:
| ID Field | Delay Reason 1 | Delay Reason 2 | Delay Reason 3 |
| 1 | Damaged | ||
| 2 | Being Repaired |
I want to build a matrix in PowerBI like below, which is all the Delay Reasons as rows and the total count for each Reason.
| Delay Reason | Total |
| Damaged | 1 |
| Being Repaired | 1 |
But, when I pull in the Delay Reason 2 after starting with the Delay Reason 1, the Delay Reason 2 becomes a sub type of Delay Reason 1 with an expand/collapse option. I instead want it to show below Delay Reason 1.
Is there a way to achieve this?
In the query editor, select your ID Field column, right click and choose "unpivot other columns". You can then delete the columns with the previous column name values (Attribute), and rename the other one (Value) to "Delay Reason". If needed, filter out any null/blank values and you can then make your visual.
Pat
2 Replies
In the query editor, select your ID Field column, right click and choose "unpivot other columns". You can then delete the columns with the previous column name values (Attribute), and rename the other one (Value) to "Delay Reason". If needed, filter out any null/blank values and you can then make your visual.
Pat
Thank you very much! That worked.
But, one issue though is that the counts are now the same for all the delay reason codes.
