Forum Discussion
Creating text column with values from two related tables
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?
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
3 Replies
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=czNHt7UXIe8This 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.
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
