Forum Discussion
Errors using 'RELATED' with new data set
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,
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,
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 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,
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?
