cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mterry Member
Member

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_NameCodeYear
TokyoAB1232017
TokyoAB1232018
KyotoAB1232019
LondonAB1242019
New YorkAB1252019
Los AngelesAB1262019
   
Spend Report  
AmountCodeSpend Date
 $           100.00AB1232/1/2017
 $           100.00AB12312/10/2017
 $           100.00AB1236/1/2018
 $           100.00AB1246/15/2018
 $           100.00AB1258/1/2019
 $           100.00AB1269/1/2019
 $           100.00XM13710/1/2019
   
Desired output  
Office_NameAmount 
Tokyo $    200.00 
Kyoto $    100.00 
London $    100.00 
New York $    100.00 
Los Angeles $    100.00 
Misc $    100.00 
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: 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 )

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

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 REPLIES 9
Community Support Team
Community Support Team

Re: Errors using 'RELATED' with new data set

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mterry Member
Member

Re: Errors using 'RELATED' with new data set

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

 

Location List  
Office_NameCodeYear
TokyoAB1232017
TokyoAB1232018
KyotoAB1232019
LondonAB1242019
New YorkAB1252019
Los AngelesAB1262019
   
Spend Report  
AmountCodeSpend Date
 $           100.00AB1232/1/2017
 $           100.00AB12312/10/2017
 $           100.00AB1236/1/2019
 $           100.00AB1246/15/2019
 $           100.00AB1258/1/2019
 $           100.00AB1269/1/2019
 $           100.00XM13710/1/2019
   
Desired output  
Office_NameAmount 
Tokyo $    200.00 
Kyoto $    100.00 
London $    100.00 
New York $    100.00 
Los Angeles $    100.00 
Misc $    100.00 
Community Support Team
Community Support Team

Re: Errors using 'RELATED' with new data set

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 )

Errors-using-RELATED-with-new-data-set

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mterry Member
Member

Re: Errors using 'RELATED' with new data set

@v-jiascu-msft

 

 

mterry Member
Member

Re: Errors using 'RELATED' with new data set

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

Community Support Team
Community Support Team

Re: Errors using 'RELATED' with new data set

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,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mterry Member
Member

Re: Errors using 'RELATED' with new data set

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?

 

 

Community Support Team
Community Support Team

Re: 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 )

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

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
mterry Member
Member

Re: Errors using 'RELATED' with new data set

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