Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
@kbauro97 , 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
Hi @kbauro97 ,
The second one. Open query editor and selecte the first three columns and use unpivot other columns feature.
Then you will get a table like below and you will be able to create matrix the visual that you wanted.
Best Regards,
Jay
Thank you. I didn't know that feature, it is really helpful
Hi @kbauro97 ,
The second one. Open query editor and selecte the first three columns and use unpivot other columns feature.
Then you will get a table like below and you will be able to create matrix the visual that you wanted.
Best Regards,
Jay
@kbauro97 , 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