Errors using 'RELATED' with new data set

[mterry]

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 original issue and solution:

https://community.powerbi.com/t5/Desktop/Catch-all-when-no-reference/td-p/377868

Here's a summary of the issue with sample data:

I have two tables, one that contains office names by year, and one that contains amounts spent at the offices by date. An office code links the two tables, and the office name can change each year while still using the same code. The table that contains the spend information does not include office names, only an office code. Sometimes a code comes through on the spend information table that doesn't exist on the office name table. What I wanted to do was a) have the office name on the output be reflective of the year, and b) have a bucket where the office name came through as 'misc' for each instance where the spend code didn't exist on the office name table. I'm getting errors when trying to repeat the solution posted in the link above. 

Location List
Office_Name	Code	Year
Tokyo	AB123	2017
Tokyo	AB123	2018
Kyoto	AB123	2019
London	AB124	2019
New York	AB125	2019
Los Angeles	AB126	2019
Spend Report
Amount	Code	Spend Date
$           100.00	AB123	2/1/2017
$           100.00	AB123	12/10/2017
$           100.00	AB123	6/1/2018
$           100.00	AB124	6/15/2018
$           100.00	AB125	8/1/2019
$           100.00	AB126	9/1/2019
$           100.00	XM137	10/1/2019
Desired output
Office_Name	Amount
Tokyo	$    200.00
Kyoto	$    100.00
London	$    100.00
New York	$    100.00
Los Angeles	$    100.00
Misc	$    100.00

[v-jiascu-msft]

Hi @mterry,

There could be two inconsistencies that make the solution complicated.

1. Kyoto is the name of 2019. Why does the desired result have "Kyoto 100"?

2. The name of AB124 doesn't exist for the year 2018.

Best Regards,

[mterry]

My apologies, looks like the dates got messed up, please see the below. 

Location List
Office_Name	Code	Year
Tokyo	AB123	2017
Tokyo	AB123	2018
Kyoto	AB123	2019
London	AB124	2019
New York	AB125	2019
Los Angeles	AB126	2019
Spend Report
Amount	Code	Spend Date
$           100.00	AB123	2/1/2017
$           100.00	AB123	12/10/2017
$           100.00	AB123	6/1/2019
$           100.00	AB124	6/15/2019
$           100.00	AB125	8/1/2019
$           100.00	AB126	9/1/2019
$           100.00	XM137	10/1/2019
Desired output
Office_Name	Amount
Tokyo	$    200.00
Kyoto	$    100.00
London	$    100.00
New York	$    100.00
Los Angeles	$    100.00
Misc	$    100.00

[v-jiascu-msft]

Hi @mterry,

It's much clear now. The Code and Year can be used as the Primary key just like in the Database. To make it simple, I would suggest you add a column in the Table [Spend Report]. Please refer to the formula and snapshot below.

Column =
VAR strictName =
    LOOKUPVALUE (
        'Location List'[Office_Name],
        'Location List'[Code], [Code],
        [Year], YEAR ( [Spend Date] )
    )
RETURN
    IF ( ISBLANK ( strictName ), "Misc", strictName )

Best Regards,

[mterry]

@v-jiascu-msft

Thanks for the help; however, when I tried to create that column, I got an error saying "A table of multiple values was supplied where a single value was expected". 

I do have multiple tables that have different spend information - is the easiest way to solve this problem to create a column on each of those? Or is there a way to have one single table (or even create that table in BI) where the 'strictName' is the default location?

[v-jiascu-msft]

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]

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 · Accepted solution]

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 )

Best Regards,

[mterry]

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

[mterry]

Wanted to bump this up to see if anyone knew why I might be getting the error I listed.