relatedtable and bringing in info

[sy898661]

Hi!

I have 2 queries in my report, and they are connected via Lot Number. I am using the on-hand quantity data from one query and the ship date data from the other to create my visuals. But, some things arent working properly, So I was wondering if it would be possible to bring in the ship date from the second table into the first table? 

The issue I am having is the way the first table is set up, there are a lot of "duplicate" values, so when I tried to add a column into the first table as: RELATEDTABLE(Table 2), it gave me an error.

Like this: (The connection is a many to many Table 1: Lot Number without Bag > Table 2: Lot Number)

If this is possible or if anyone has any other ideas, please let me know! 🙂 ❤️

Thank you!!!

[sy898661]

@amitchandak @v-shex-msft thank you both so much I really appreciate it!!! 🙂

[v-shex-msft]

HI @sy898661,

I'd like to suggest you add a calculated column to table1 with lookupvalue function to search values based on current Lot Number:

Ship =
LOOKUPVALUE (
    'Table 2'[Ship Date],
    'Table 2'[Lot Number], 'Table'[Lot Number],
    MAX ( 'Table 2'[Ship Date] )
)

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

Create as a new column

Ship date in table 1 =maxx(filter(table2,table2[LotNumber]=table1[LotNumber]),Table2[Ship Date])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin