Forum Discussion
Database design and modeling
Hi,
I want to create a dashboard where I would have a matrix with the categories divided per date and its chart per date and value.
For example, I would have a table with the countries, then I would find the cities, and then the division per indicators.
I have two options. In the first table, I have in the first column the date and the next columns the values for Country/City/Indicator combination. I will have many combinations and I want to have all the values from all the year in the table.
| Country | Mexico | Mexico | USA | USA |
| City | Cdmx | Cdmx | NYC | NYC |
| Indicator | People | Quality | People | Quality |
| 01-01-22 | 100 | 10 | 150 | 10 |
| 02-01-22 | 200 | 20 | 250 | 20 |
| 03-01-22 | 300 | 10 | 350 | 10 |
| 04-01-22 | 400 | 20 | 450 | 20 |
| 05-01-22 | 500 | 10 | 550 | 10 |
| 06-01-22 | 600 | 20 | 650 | 20 |
| 07-01-22 | 700 | 10 | 750 | 10 |
| 08-01-22 | 800 | 20 | 850 | 20 |
Vertical
In the second option, I have the categories in columns (country, city, indicator) and one date per column.
| Country | City | Indicator | 01-01-22 | 02-01-22 | 03-01-22 | 04-01-22 | 05-01-22 | 06-01-22 | 07-01-22 | 08-01-22 |
| Mexico | Cdmx | People | 100 | 200 | 300 | 400 | 500 | 600 | 700 | 800 |
| Mexico | Cdmx | Quality | 10 | 20 | 10 | 20 | 10 | 20 | 10 | 20 |
| USA | NYC | People | 150 | 250 | 350 | 450 | 550 | 650 | 750 | 850 |
| USA | NYC | Quality | 10 | 20 | 10 | 20 | 10 | 20 | 10 | 20 |
Horizontal
Which database design should I use in order to have the dates- country/city/indicator matrix table?
How can I create the data model in order to use a matrix chart?
I hope you can help me to solve this issue. Thank you.
Anonymous , You have to unpivot the tables and bring city and date on rows.
https://radacad.com/pivot-and-unpivot-with-power-bi
Then create common dimensions like city and date join with both tables and use those
3 Replies
Anonymous , You have to unpivot the tables and bring city and date on rows.
https://radacad.com/pivot-and-unpivot-with-power-bi
Then create common dimensions like city and date join with both tables and use those