Forum Discussion
Creating a matrix for the underlying data in Power BI
I have a table that has columns like region, city, phase, start work week number, authorisation week number and application week number, as shown below,
| Region | City's | Phase | start work week number | Authorization week number | Application week number |
| Osa | Dedxxxxt | Cluster 2 | 9 | 7 | 6 |
| Osa | Dede1 | Cluster 2 | 9 | 7 | 6 |
| Osa | Dede2 | Cluster 2 | 9 | 7 | 6 |
| SXXX | EDDD | Cluster 2 | 9 | 7 | 6 |
| SXXX | EDDD1 | Cluster 2 | 9 | 7 | 6 |
| GXXX | HCCC | Cluster 2 | 9 | 7 | 6 |
| GXXX | HCCC1 | Cluster 1 | 9 | 7 | 6 |
| GXXX | HCCC2 | Cluster 1 | 9 | 7 | 6 |
| GXXX | HCCC3 | Cluster 1 | 9 | 7 | 6 |
| Osa | LDDD | Cluster 1 | 9 | 7 | 6 |
| Osa | LDDD1 | Cluster 1 | 9 | 7 | 6 |
| SXXX | MSSS | Cluster 1 | 9 | 7 | 6 |
| SXXX | MSSS1 | Cluster 1 | 9 | 7 | 6 |
| SXXX | MSSS2 | Cluster 1 | 9 | 7 | 6 |
I need to create a matrix in power BI like below,
I tried, but encountering issues with rows and columns and relationships in Power BI, what i tried to do is that I duplicated the source table for application, authorisation and start work, and created a week number table and tried to create relationships between the week number table and a source regoin table but that created ambiguities and relationship is not working.
Can I anyone help me in starting this work?
Anonymous , I made an attempt, not sure that what you need. If not need an example on what you need
Please find the file attached after signature
amitchandak thanks Amit, I got it with the way you suggested!!
4 Replies
- amitchandak
Super User
Anonymous , I think you need a common week table joined to three table with one active and on inactive join and measure to activate the relationship
example
amitchandak can you show with the above data if possible, I created a common week table and three active relationships with the three tables. But not able to create active relationship for the row header (that is phase, region, city) for the matrix, with the three tables.
- amitchandak
Anonymous , I made an attempt, not sure that what you need. If not need an example on what you need
Please find the file attached after signature
amitchandak thanks Amit, I got it with the way you suggested!!