Forum Discussion
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 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 |
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,
9 Replies
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,
