Forum Discussion

mterry's avatar
mterry
Helper V
7 years ago
Solved

Errors using 'RELATED' with new data set

I originally received a resolution to this problem, but since have had to move on to slightly new data sets and am having an issue using the solutions posted in the link. Here's a link to the origina...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi mterry,

     

    Please download the demo from the attachment. 

    I added a custom index in the [Location List] from the Query editor. Now only one value will be matched.

    let code_p = [Code], year_p = [Year], index_p =[Index] in List.Accumulate(Table.SelectRows(#"Added Index", each [Code] = code_p and  [Year] = year_p and [Index] <= index_p)[Office_Name], 0, (index, value) => index + 1)
    Column =
VAR strictName =
    LOOKUPVALUE (
        'Location List'[Office_Name],
        'Location List'[Code], [Code],
        [Year], YEAR ( [Spend Date] ),
        [CustomIndex], 1
    )
RETURN
    IF ( ISBLANK ( strictName ), "Misc", strictName )

    Errors-using-RELATED-with-new-data-set2

     

     

    Best Regards,

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi mterry,

 

Firstly, it's a column.

Secondly, can the [code] and the [year] determine an [Office_Name] in the location table? For example, no such situation: 

Tokyo AB123 2018
Kyoto AB123 2018

Can you share your test file and some snapshots? Please mask the sensitive parts first.

 

Best Regards,

mterry's avatar
mterry
Helper V
7 years ago

Edit: I said there weren't scenarios like the one you noted, however I went through the data line by line, and I did find instances where the same code referred to more than one name in the same year. I'm not sure if there are ways around this, for instance to only use the 'first' name?

 

 

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi mterry,

     

    Please download the demo from the attachment. 

    I added a custom index in the [Location List] from the Query editor. Now only one value will be matched.

    let code_p = [Code], year_p = [Year], index_p =[Index] in List.Accumulate(Table.SelectRows(#"Added Index", each [Code] = code_p and  [Year] = year_p and [Index] <= index_p)[Office_Name], 0, (index, value) => index + 1)
    Column =
VAR strictName =
    LOOKUPVALUE (
        'Location List'[Office_Name],
        'Location List'[Code], [Code],
        [Year], YEAR ( [Spend Date] ),
        [CustomIndex], 1
    )
RETURN
    IF ( ISBLANK ( strictName ), "Misc", strictName )

    Errors-using-RELATED-with-new-data-set2

     

     

    Best Regards,

    • mterry's avatar
      mterry
      Helper V
      7 years ago

      Thank you so much for the help (and your time)!