# Thread: Relationship between tables doesn't seem to work for some values Thanks: 0 Likes: 0

1. ## Relationship between tables doesn't seem to work for some values

Hi, I have two tables, one data table and one lookup table.

However, the lookup table doesn't seem to work for all values of the lookup table below:

Code:
```0

1-5

6-15

16-19

20-29

30-39

40-49

50-64

65-74

75-79

80-89

90-w

```
For lookup table value of 1-5 and 6-15 the relationship doesn't seem to work and the values gives me a total instead of a part of a sum. I am thinking that this could be because of sorting or something similar to that.

Is there any workaround for this? In the image below, the 6-15 should give me 5235 and it gives me 44955 and I don't know why??

https://ibb.co/r6QQG1y

2. ## Re: Relationship between tables doesn't seem to work for some values

Any suggestions on how to fix this problem? It really bothers me that I can't figure out why the lookup doesn't work!

4. ## Re: Relationship between tables doesn't seem to work for some values

Would you post the sample using one of the methods mentioned in:

5. ## Re: Relationship between tables doesn't seem to work for some values

A B C
1 City Age 2017
14 City (Suburb 1) 0 1135
15 City (Suburb 1) 1-5 4395
16 City (Suburb 1) 6-15 5455
17 City (Suburb 1) 16-19 1835
18 City (Suburb 1) 20-29 14265
19 City (Suburb 1) 30-39 15155
20 City (Suburb 1) 40-49 8720
21 City (Suburb 1) 50-64 10515
22 City (Suburb 1) 65-74 5105
23 City (Suburb 1) 75-79 1475
24 City (Suburb 1) 80-89 1645
25 City (Suburb 1) 90-w 410

Column A is city name, column B is the same as my lookup table, column C is the population in that age group.

The lookup table works for age 0, 16-19, 20-29, 30-39, 40-49, 50-64, 65-74, 75-79, 80-80 and 90-w.

It doesn't work for age 1-5 and 6-15.

6. ## Re: Relationship between tables doesn't seem to work for some values

Could age 1-5 and 6-15 be interpreted as dates?

7. ## Re: Relationship between tables doesn't seem to work for some values

Originally Posted by Waimea
Could age 1-5 and 6-15 be interpreted as dates?
Change the set up like below:

ABCD
1CityAge2017
14City (Suburb 1)01135
15City (Suburb 1)154395
16City (Suburb 1)6155455
17City (Suburb 1)16191835
18City (Suburb 1)202914265
19City (Suburb 1)303915155
20City (Suburb 1)40498720
21City (Suburb 1)506410515
22City (Suburb 1)65745105
23City (Suburb 1)75791475
24City (Suburb 1)80891645
25City (Suburb 1)90w410

Sheet1

8. ## Re: Relationship between tables doesn't seem to work for some values

I have changed my setup as per your very clever suggestion!

I am using PowerPivot but I am unsure how to set up my lookup table?

Code:
`2017 1-5:=CALCULATE([Population 2017] ;'Age'[Age] >="1" && 'Age'[Age] <="5")`

9. ## Re: Relationship between tables doesn't seem to work for some values

I got it to work. Thank you very much Aladin!

Your suggestion is really good and clever!

10. ## Re: Relationship between tables doesn't seem to work for some values

If someone has the time, please show me how to set up my lookup tablesfor this set up??