Database design and modeling

[kbauro97]

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.

[amitchandak · Accepted 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

[kbauro97]

Thank you. I didn't know that feature, it is really helpful

[v-jayw-msft · Accepted solution]

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

[amitchandak · Accepted 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