Powerbi Relationship

[Delyan_Kirilov]

Hi team,

I have a case where I want to create a relationship between two tables. One carries timepoints and the header row is the identifier. Looks like this:

The second carries metadata for the same series, where the identifier is now a column.

My ultimate goal is to be able to create a relationship so that I can apply in the report a filter of let's say 'Freq = Monthly' or 'Country = United States' and that should filter only the applicable Series Names and their timepoints from the first table.

I've been struggling with this for the past hour. Let me know if you have come across the same situation.

Thanks in advance!

[Dinesh_Suranga]

Hi @Delyan_Kirilov ,

Select the first row of the first table and unpivot other columns. Then you can create the relationship.

If you want you can create unique Series Name table to.

Thanks
Dinesh

[Delyan_Kirilov]

Hi Dinesh,

Thank you for your quick help. I've tried this in the beginning and the relationship is possible then. The issue is that in this form the timeseries are not very usable in the visualisation panel. The are grouped under one attribute and plotting two economic variables now becomes very hard and not native for PowerBi. 

[Anonymous]

Thank @Dinesh_Suranga  for your prompt reply.

Hi @Delyan_Kirilov ,

Can you provide detailed data (without sensitive information) and provide the outputs expected for the economic variables. This will allow us to better understand and solve the problem you are having.

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired out put and pbix file without privacy information.

Best Regards,

Ada Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Delyan_Kirilov]

Hi Ada,

Thank you for assisting me. The community portal doesn't allow me to attach an xlsx file, but let me describe the issue below.

Here's an example table. It has more than one metadata description (name of the indicator, country, frequency, etc.). 

In the conventional table, we only have one column header (indicator name). The idea is to be able to create a dashboard to allow users to filter with all other metadata fields too. Imagine charts, where you click on a slicers with 'countries' or 'frequencies' and that filters series with only that attribute. Do you know an elegant way to do that?

Series Name	(DC)Exports: fob: Advanced Economies: Italy	(DC)Exports: fob: Advanced Economies: Spain	(DC)Exports: fob: Advanced Economies: Portugal	(DC)Exports: fob: Advanced Economies: Portugal	(DC)Exports: fob: Advanced Economies: Ireland	(DC)Exports: fob: Advanced Economies: Ireland	(DC)Exports: fob: Advanced Economies: Greece
Region	Italy	Spain	Portugal	Portugal	Ireland	Ireland	Greece
Frequency	Quarterly, ending "Mar, June, Sep, Dec"	Annual, ending "Dec" of each year	Quarterly, ending "Mar, June, Sep, Dec"	Monthly	Monthly	Annual, ending "Dec" of each year	Monthly
Unit	USD mn	USD mn	USD mn	USD mn	USD mn	USD mn	USD mn
Source	International Monetary Fund	International Monetary Fund	International Monetary Fund	International Monetary Fund	International Monetary Fund	International Monetary Fund	International Monetary Fund
Status	Discontinued	Discontinued	Discontinued	Discontinued	Discontinued	Discontinued	Discontinued
Series ID	2.6E+08	2.6E+08	2.6E+08	2.6E+08	2.6E+08	2.6E+08	2.6E+08
Last Update Time	#######	#######	#######	#######	#######	#######	#######
12-1996	7290.22	15627.40	799.44	262.32	1125.58	11709.00	340.24
01-1997				260.06	1091.53		241.71
02-1997				229.96	1053.74		260.13
03-1997	6931.79		748.68	258.66	1160.87		300.77
04-1997				262.62	1109.59		292.10
05-1997				271.09	1160.51		287.95
06-1997	7269.05		778.15	244.44	1117.17		310.96
07-1997				271.42	1014.84		312.90
08-1997				254.22	1030.25		295.87
09-1997	6757.52		747.10	221.46	1121.26		337.36
10-1997				272.19	1264.57		315.67
11-1997				242.94	1310.69		284.18
12-1997	8022.05	16817.30	789.13	273.88	1253.24	13688.40	322.80
01-1998				228.99	1128.11		299.40