Find text in different columns and different tables

[JoaoLourenco]

Good afternoon,

I would like help with the following:

1.I want to know if the data from the [Registration] field in the 'Vehicles' table is found in the [Asset Elements Description] field in the 'Model 32' table

If true, then write for example "YES"

If false, then write for example "NO".

I've tried several functions from Search, FIND, CONTAINS and I couldn't.

I apreciate your help.

Thank you!

[muhammad_786_1 · Accepted solution]

You can create a new calculated column in Table 1 that checks whether the [ID_Matr] in Table 1 exists in the [Designation] column of Table 2.

Steps:

1- Navigate to the "Data" view.

2- Select Table 1 where you want to create the new calculated column.

3- Create a new column by clicking on "New Column".

4- Enter the following DAX formula:

ExistsInTable2 =
IF(
COUNTROWS(
FILTER(
'Table 2',
SEARCH('Table 1'[ID_Matr], 'Table 2'[Designation], 1, 0) > 0
)
) > 0,
"YES",
"NO"
)

5- Now, new column ExistsInTable2 will now show "YES" if the [ID_Matr] exists in [Designation] of Table 2 otherwise "NO".

[JoaoLourenco]

Hi @muhammad_786_1 ,

It worked perfectly like I wish.

Thank you very much for your help.

Regards,

Joao

[rajendraongole1]

Hi @JoaoLourenco - Can you please share the information on a dummy form in excel or copy text with expectations with limited data? it helps us to analyse and share the expectations. 

[JoaoLourenco]

Hi @rajendraongole1 ,

Thank you for your reply.

Sure yes!

Let's see.

I want to check if the [ID Matr] of Table 1 exists in the [Designation] of Table 2.

If true, return "YES" 

If false, retun "No"

(for example, or maybe can count how many times found that). 

Table 1

ID_Matr

60-95-IG

72-TG-18

59-VM-35

AE-59-UJ

91-69-XU

91-71-XU

10-CA-75

54-HB-73

96-SS-96

Table 2

Cód	Designation
2375	IM001880 TD 91-69-XU Veículos Automóveis Ligeiros & Mistos
2375	IM001881 TD91-69-XU Veículos Automóveis Ligeiros & Mistos
2375	IM001884 TD 91-71-XU Veículos Automóveis Ligeiros & Mistos
2040	IM000678 Furo Covelo Trabalhos Hidráulicos Incl. Poços ÁguaTM
2385	IM001888 Nissan 10-CA-75 Veículos Automóveis Pesados com Atrelado
2375	IM001886 Nissan 54-HB-73 Veículos Automóveis Ligeiros & Mistos
2040	IM000680 Furos - Abertura Trabalhos Hidráulicos Incl. Poços ÁguaTM
2385	IM001892  Isuzu 72-TG-18 Veículos Automóveis Pesados com Atrelado
2040	IM000681 Furos Sisa Trabalhos Hidráulicos Incl. Poços ÁguaTM

Thank you!