Forum Discussion

TestBI's avatar
TestBI
New Member
6 years ago

Need assistant

Hi,

I have two table 1st consist of ISD number and in 2nd table I have range and country name.

From 1st table I took 1st 8 digit of ISD number. Now I want to find this 8 digit number follow in which country from my 2nd table. But there is no common column or any criteria in these two table, so I have added one more column in my 2nd table   as ISD to make relation in two table still give me error. Please see the below error 

My Formula is : 

country = IF('Hyd Dec CDR 2018 '[Call Type]="ISD",IF(AND('Hyd Dec CDR 2018 '[ISD Type]>=RELATED(Sheet1[End Area Code2]),RELATED(Sheet1[End Area Code2])),RELATED(Sheet1[Country]),'Hyd Dec CDR 2018 '[Call Type]))
Error
The column 'Sheet1[Country]' either doesn't exist or doesn't have a relationship to any table available in the current context.

 

1st Table    (Hyd Dec CDR 2018)                                                   2nd table ((Sheet1)

finalCalledPartyNumberCall TypeISD TypeISD Type CountryStart Area CodeEnd Area CodeCall Type
0018043000ISD0018043000180430 US & Canada [Toll-free - 001844]0018440000184499ISD
00440140771ISD0044014000440140 US & Canada [ISD]0018890000188999ISD
0044012682ISD0044012600440126 US & Canada [ISD]0018780000187899ISD
00440140771ISD0044014000440140 US & Canada [ISD]0018670000186700ISD
0018004428ISD0018004400180044 US & Canada [ISD]0018560000185699ISD
0018004428ISD0018004400180044 US & Canada [ISD]0018450000184599ISD
0018004428ISD0018004400180044 US & Canada [ISD]0018010000180199ISD
0018004428ISD0018004400180044 US & Canada [ISD]0018000000180099ISD
0018004428ISD0018004400180044 UK0044000000449999ISD
0018004428ISD0018004400180044     
00440140325ISD0044014000440140     

Please help,

 

Regards,

Sandeep Joshi

Need Help

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    TestBI you cannot use related  function on non-related tables, use lookupvalue function to get value from non related tables.

  • dax's avatar
    dax
    Community Support
    6 years ago

    Hi TestBI, 

    It seems that you don't have relationship between two tables, right? If so , I think you need to create relationships between them, then use measue to see whether it work or not. By the way, if possible, could you please inform me more detailed information(such as your sample data and your expected output)? Then I will help you more correctly.

    Best Regards,
    Zoe Zhi

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