Creating text column with values from two related tables

[ReadTheIron]

I'm trying to create a text column displaying information from two related tables:

Asset List
AssetFull	Latest Failure
Asset1	1/2/2021
Asset2	3/6/2021
Asset3	4/10/2021

 and

Daily Reports
AssetFull	Asset ID
Asset1	1
Asset2	2
Asset1	1
Asset3	3

They have a many-to-one relationship, Daily Reports to Asset List. I'm trying to create a text column that displays Asset ID and Latest Failure. 

Asset List
AssetFull	Latest Failure	IDDate
Asset1	1/2/2021	1: 1/2/21
Asset2	3/6/2021	2: 3/6/21
Asset3	4/10/2021	3: 4/10/21

I've tried IDDate = FILTER(RELATED('Daily Reports'),'Asset List'[AssetFull])) & ":" & 'Asset List'[Latest Failure], but this gives me a syntax error.

Relatedly, when I was testing this with values in the same table, the date value displayed as the full date/time (Asset1: 01/02/2021 12:00 AM) - is there a way to display only the short date in the new column?

[Anonymous · Accepted solution]

Hi  @ReadTheIron ,

Here are the steps you can follow：

1. Create calculated column.

Date =
VAR _1=CALCULATE(MAX('Daily Reports'[Asset ID]),FILTER(ALL('Daily Reports'),'Daily Reports'[AssetFull]=EARLIER('Asset List'[AssetFull])))
return
_1&":"&[Latest Failure]

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[amitchandak]

@ReadTheIron , Filter create a table or filter in measure or column?

related can not be used to bring data from many side

what output you need ?

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[ReadTheIron]

This is the output I need:

Asset List
AssetFull	Latest Failure	IDDate
Asset1	1/2/2021	1: 1/2/21
Asset2	3/6/2021	2: 3/6/21
Asset3	4/10/2021	3: 4/10/21

Thanks for the video links; I'm reviewing them now.