topic Re: Create a calculated columns from 2 tables in DAX Commands and Tips

Create a calculated columns from 2 tables

marabeda — Sun, 03 Jul 2022 07:02:57 GMT

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

Re: Create a calculated columns from 2 tables

technolog — Sun, 03 Jul 2022 07:22:26 GMT

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")

Re: Create a calculated columns from 2 tables

tamerj1 — Sun, 03 Jul 2022 14:16:07 GMT

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

Re: Create a calculated columns from 2 tables

technolog — Sun, 03 Jul 2022 14:24:35 GMT

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")

Re: Create a calculated columns from 2 tables

technolog — Sun, 03 Jul 2022 14:34:16 GMT

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

Re: Create a calculated columns from 2 tables

marabeda — Sun, 03 Jul 2022 20:25:13 GMT

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

Re: Create a calculated columns from 2 tables

marabeda — Sun, 03 Jul 2022 20:38:15 GMT

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

Re: Create a calculated columns from 2 tables

technolog — Mon, 04 Jul 2022 08:50:22 GMT

marabeda Do you mean something like this in results?

Re: Create a calculated columns from 2 tables

marabeda — Mon, 04 Jul 2022 09:26:09 GMT

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

Re: Create a calculated columns from 2 tables

technolog — Mon, 04 Jul 2022 09:49:59 GMT

It's better version

We concatenate by two columns here

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

Re: Create a calculated columns from 2 tables

marabeda — Mon, 04 Jul 2022 13:12:12 GMT

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

Re: Create a calculated columns from 2 tables

technolog — Tue, 05 Jul 2022 12:48:59 GMT

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

Text me when it works.

Re: Create a calculated columns from 2 tables

technolog — Wed, 06 Jul 2022 07:50:07 GMT

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

Re: Create a calculated columns from 2 tables

v-chenwuz-msft — Wed, 06 Jul 2022 09:31:19 GMT

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