How to link two lookup tables

[Anonymous]

Hi,

I need help in building relationship between the two look up tables to enable the working of fliters.

I have two lookup tables Location and Parent table.

Location Table contains- Level 1- world, Level 2-Country-India, Level 3 -States of India like Andhrapradesh, karnatak and all.

 Parent Table contains Level 1- layer, Level 2- indicators like GDP, GSDP, Vehicle registration data.

In the above u can see. I have selected GDP which has only India level data but in lcoation slicer even state values are showing up in the location filter on the left side of image.

[amitchandak]

@Anonymous , not very clear.

Try https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-february-2020-feature-summary/#_Hierarchical_slicer

Create a new hierarchy
https://www.tutorialgateway.org/create-hierarchy-in-power-bi/
https://www.youtube.com/watch?v=x6vXVJZ_eTY

or use this

https://www.youtube.com/watch?v=cyOquvfhzNM

[Anonymous]

Thanks for the Immediate Reponse.

Let me explain how my data sets are.

Indicator	Location	Remark
Level 1	Level 2
Economic layer	GDP	India	It has India level data
IIP	India	It has India level data
GSDP	State	It has State level data
Social layer	Vehicle Registration	State	It has State level data
Rainfall	District	It has State level data

In the above you can see Indicator table which is one of the lookup table and the other is location table. ( For both i have created level using parrent child relatioships).

My problem is- As you can see GDP is having values at India levle which is country level but if select GDP in the silcer the location slicer should only display me india but it shoing the state name as well. 

Is thier any way to solve this issue.

Can we create link between two lookup tables which is not having any common columns?.

[Anonymous]

Indicator	Indicator	Location	Remark
Level 1	Level 2	Location	Remark
Economic layer	GDP	India	It has India level data
Economic layer	IIP	India	It has India level data
Economic layer	GSDP	State	It has State level data
Social layer	Vehicle Registration	State	It has State level data
Social layer	Rainfall	District	It has State level data

[v-kelly-msft]

Hi  @Anonymous ,

So you wanna get the result marked in the red rectangle?

If so ,could you pls provide more details of raw data?Not sure how the result come out..

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Give me sometime i will upload it googledrive and share it across

[v-kelly-msft]

Hi @Anonymous ,

You can also upload the file to onedrive business and share the link with us.

Much appreciated.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Hi Kelly,

Thanks for the Repsone.

Here is my raw data. 

GDP Raw data

Parameter Type	Indicator	Country	Location_ID	Variable Type	Year	Date	Sector	Item ID	Value in Crs	Value
Economic Parameter	GDP	India	2	Socio Economic	2012	2013-01-01	Agriculture, forestry & fishing	EI24	15	15.02
Economic Parameter	GDP	India	2	Socio Economic	2013	2014-01-01	Agriculture, forestry & fishing	EI24	15	15.24
Economic Parameter	GDP	India	2	Socio Economic	2014	2015-01-01	Agriculture, forestry & fishing	EI24	16	16.09
Economic Parameter	GDP	India	2	Socio Economic	2015	2016-01-01	Agriculture, forestry & fishing	EI24	16	16.06
Economic Parameter	GDP	India	2	Socio Economic	2016	2017-01-01	Agriculture, forestry & fishing	EI24	16	16.16
Economic Parameter	GDP	India	2	Socio Economic	2017	2018-01-01	Agriculture, forestry & fishing	EI24	17	17.17
Economic Parameter	GDP	India	2	Socio Economic	2018	2019-01-01	Agriculture, forestry & fishing	EI24	18	18.03
Economic Parameter	GDP	India	2	Socio Economic	2019	2012-01-01	Agriculture, forestry & fishing	EI25	19	18.56
Economic Parameter	GDP	India	2	Socio Economic	2012	2013-01-01	Mining & quarrying	EI25	3	2.61
Economic Parameter	GDP	India	2	Socio Economic	2013	2014-01-01	Mining & quarrying	EI25	3	2.63
Economic Parameter	GDP	India	2	Socio Economic	2014	2015-01-01	Mining & quarrying	EI25	3	2.63
Economic Parameter	GDP	India	2	Socio Economic	2015	2016-01-01	Mining & quarrying	EI25	3	2.89
Economic Parameter	GDP	India	2	Socio Economic	2016	2017-01-01	Mining & quarrying	EI25	3	3.18
Economic Parameter	GDP	India	2	Socio Economic	2017	2018-01-01	Mining & quarrying	EI25	3	3.48
Economic Parameter	GDP	India	2	Socio Economic	2018	2019-01-01	Mining & quarrying	EI25	4	3.66
Economic Parameter	GDP	India	2	Socio Economic	2019	2012-01-01	Mining & quarrying	EI26	4	3.71

GSDP Data

State	Country	Location_ID	Indicator	Sector	Item ID	Concatenate2	Attribute	Year	Date	Value
Andaman & Nicobar Islands	India	3	GSDP	Agriculture, forestry and fishing	EI34	EI34-3	2011-12	2012	2012-01-01	0.005971
Andaman & Nicobar Islands	India	3	GSDP	Agriculture, forestry and fishing	EI34	EI34-3	2012-13	2013	2013-01-01	0.006122
Andaman & Nicobar Islands	India	3	GSDP	Agriculture, forestry and fishing	EI34	EI34-3	2013-14	2014	2014-01-01	0.006579
Andaman & Nicobar Islands	India	3	GSDP	Agriculture, forestry and fishing	EI34	EI34-3	2014-15	2015	2015-01-01	0.006866
Andaman & Nicobar Islands	India	3	GSDP	Agriculture, forestry and fishing	EI34	EI34-3	2015-16	2016	2016-01-01	0.006588
Andaman & Nicobar Islands	India	3	GSDP	Agriculture, forestry and fishing	EI34	EI34-3	2016-17	2017	2017-01-01	0.006497
Andaman & Nicobar Islands	India	3	GSDP	Agriculture, forestry and fishing	EI34	EI34-3	2017-18	2018	2018-01-01	0.007258
Andaman & Nicobar Islands	India	3	GSDP	Agriculture, forestry and fishing	EI34	EI34-3	2018-19	2019	2019-01-01	0.007258
Andhra Pradesh	India	4	GSDP	Agriculture, forestry and fishing	EI34	EI34-4	2011-12	2012	2012-01-01	0.940081
Andhra Pradesh	India	4	GSDP	Agriculture, forestry and fishing	EI34	EI34-4	2012-13	2013	2013-01-01	0.978311
Andhra Pradesh	India	4	GSDP	Agriculture, forestry and fishing	EI34	EI34-4	2013-14	2014	2014-01-01	1.083527
Andhra Pradesh	India	4	GSDP	Agriculture, forestry and fishing	EI34	EI34-4	2014-15	2015	2015-01-01	1.121999
Andhra Pradesh	India	4	GSDP	Agriculture, forestry and fishing	EI34	EI34-4	2015-16	2016	2016-01-01	1.215183
Andhra Pradesh	India	4	GSDP	Agriculture, forestry and fishing	EI34	EI34-4	2016-17	2017	2017-01-01	1.397172
Andhra Pradesh	India	4	GSDP	Agriculture, forestry and fishing	EI34	EI34-4	2017-18	2018	2018-01-01	1.652711
Andhra Pradesh	India	4	GSDP	Agriculture, forestry and fishing	EI34	EI34-4	2018-19	2019	2019-01-01	1.716249
Arunachal Pradesh	India	5	GSDP	Agriculture, forestry and fishing	EI34	EI34-5	2011-12	2012	2012-01-01	0.045551