Create a calculated columns from 2 tables

[marabeda]

Dear Community, I have these 2 tables:

Table1

Table2

The relationship between tables is many-to-many with column Client

My objective is to have two calculated columns in Table1 where the first column tell me if for the client the Table1[Giorno] is equal to Table2[Giorni in numeri] in this case the answer is "OK" or "NOT OK". The second column shows me the value in Table2[ore] for the specific client in Table1, where Table1[colonna giorno calcolato] is equal to "OK".

I try to compute some code, but I am too inexpert.

Anyone can help me?

Thank you

[technolog]

I find more simple solution

For Table1

Colonna calc = IF(COUNTROWS(RELATEDTABLE(Table2))>0,"OK", "NOT OK")

and for Table2

Colonna calc = IF(COUNTROWS(RELATEDTABLE(Table1))>0,"OK", "NOT OK")

You can download solution here:

https://1drv.ms/u/s!AiNTN-Usixb7h9JRrqQ7MwoAVcdl5Q?e=qWtmSM

[marabeda]

Hi, thank you for your reply, appreciated, but probably I was not so clear.

What I need to know in Table1 is: 

1) Is the client in Table1[clienti]=Table2[clienti] if the answer is yes then

2) Is  Table1[giorni]=Table2[giorni in numeri]? If the answer is yes, then is OK.

My aim is to be sure that my client has the job done exactly at the right day of the week.

Thank you again

[technolog]

@marabeda Do you mean something like this in results?

[marabeda]

Hi Technolog, yes is correct. Now I try your solution on my original table to check if it works and I let you know.

I am gratefull for you prompt replay.

Marabeda

[technolog · Accepted solution]

It's better version 

We concatenate by two columns here

Source file: https://1drv.ms/u/s!AiNTN-Usixb7h9JTE5bZIVEGbQGfUQ?e=2PK2tO

[marabeda]

Hi technolog, it works! thank you. I solved first part of the question. The second part is how to show the value in table2[ore] in a column that match client in table1. Can you help? Can I write the same formula?

Many Thx

[v-chenwuz-msft]

Hi @marabeda ,

I am very happy that your problem is solved, please mark it so that more people with the same problem as you can find the answer quickly!

Best Regards

Community Support Team _ chenwu zhu

[technolog]

@marabeda is it all right? Are you need more help?

[technolog]

Yes, you can use the same formula - only replace Table2 to Table1 and all will work. 

Text me when it works.

[technolog]

Before this

Colonna giorno calc = IF(NOT(ISBLANK(RELATED('Table2'[Cliente']))), "OK", "NOT OK")

You can create calculated table

Table2Unique = DISTINCT('Table2'[Cliente])

and then use

Colonna giorno calc = IF(NOT(ISBLANK(RELATED('Table2Unique'[Cliente']))), "OK", "NOT OK")

[tamerj1]

Hi @marabeda 

The RELATEDTABLE ( Table2 ) will return two rows for each row in Table1. There must be another column to be matched in order to filter it down to one row. Ror examole a date or a month. Otherwise this cannot be achieved 

[marabeda]

Thank you for your interest. I do not have another column. These are the 2 tables. I can avoid having two row only if I create a table like this:

where the headers are the days of the week and each row contains the name of the client and when I have to execute the job for the client.

But with this table, I do not know how to verify in Table1 if the day (Table1[giorno]) when I have executed the job is the day that I have programmed in Table2.

I hope this help to explain better what I want to achieve.

Thank you

[technolog]

Hi!

You need to use IF and RELATED

For Table 1:

Colonna giorno calc = IF(NOT(ISBLANK(RELATED('Table2'[Cliente']))), "OK", "NOT OK")

For Table 2 the same:

Colonna giorno calc = IF(NOT(ISBLANK(RELATED('Table1'[Cliente']))), "OK", "NOT OK")