Forum Discussion

marri's avatar
marri
Frequent Visitor
8 years ago
Solved

related function between three tables

Hi,

 

I have three tables say A, B and C.

Relationship Between tables are follows:

Table A has Primarykeys of B and C

B to A - One to Many

C to A - One to Many

 

Now I want to derive a column in table A (calculated column) based on the combition of some descriptive columns of B and C

For Example:  Table A.calculated col  =SWITCH(TRUE(),

                                                               tableB.title = 'abc' and tablec.code='kon', 'pi'

                                                              tableB.title='phg' and tableC.code='poj', 'lm',

                                                             ' unknown')

 

Please explain how shall i use related function for the above scenario

 

 

  • v-caliao-msft's avatar
    v-caliao-msft
    8 years ago

    marri,

     

    If the table relationships are created without problems. You could try the DAX below.

    Table A.calculated col =
var tableBtitle = RELATED(tableB[title])
var tableCcode = RELATED(tableC[code])
return SWITCH(TRUE(),   tableBtitle  = "abc" && tableCcode ="kon", "pi",
			tableBtitle ="phg" && tableCcode ="poj", "lm",
			"unknown")

    Regards,

    Charlie Liao

3 Replies

  • marri's avatar
    marri
    Frequent Visitor
    8 years ago

    Hi,

     

    I have three tables say A,B and C

    The relationship between tables are listed below

     

    Table A-B - Many to One

    Table A-C - Many to One

    Table A has primary key of B and C as different columns

    Now I want to derive a column in table A based on the combination of values of columns of Table B and C


       For Eample:  Calculated column in table A =SWITCH(TRUE(),
                                                                                         A.title="Bc" && B.code="lo", "pf",
                                                                                        A.title="ko" && B.code="rt", "ju",
                                                                                         A.title="we","pm"
                                                                                          "Unknown"
                                                                                          )

     

    Pleae explain me how to perform the above using related function

     

    Thanks In Advance
                                                                        

  • v-caliao-msft's avatar
    v-caliao-msft
    Microsoft Employee
    8 years ago

    marri,

     

    If the table relationships are created without problems. You could try the DAX below.

    Table A.calculated col =
var tableBtitle = RELATED(tableB[title])
var tableCcode = RELATED(tableC[code])
return SWITCH(TRUE(),   tableBtitle  = "abc" && tableCcode ="kon", "pi",
			tableBtitle ="phg" && tableCcode ="poj", "lm",
			"unknown")

    Regards,

    Charlie Liao

    • marri's avatar
      marri
      Frequent Visitor
      8 years ago

      Thankyou i have followed your steps, It helped me.