Forum Discussion

marabeda's avatar
marabeda
Helper I
4 years ago
Solved

Create a calculated columns from 2 tables

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

 

13 Replies

  • technolog's avatar
    technolog
    Super User
    4 years ago

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

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
      marabeda
      Helper I
      4 years ago

      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's avatar
    technolog
    Super User
    4 years ago

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

     

    • marabeda's avatar
      marabeda
      Helper I
      4 years ago

      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