Forum Discussion
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...
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,
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?
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,
Thank you so much for the help (and your time)!