topic Re: create calculated column based on 3 tables in DAX Commands and Tips

create calculated column based on 3 tables

mhusaein — Tue, 22 Nov 2022 17:35:08 GMT

i have 3 tables as follows

table 1 :

table 2 :

table 3 :

i need to get the sum of table1[value] and instert it as a column in table[3] as i will make extra calculations after this

Relations are as follows:

table1[code] & table2[code] , many to one.

table2[type] & table3[type] , one to many.

id column in table1 and table 3 has duplicates

Re: create calculated column based on 3 tables

tamerj1 — Tue, 22 Nov 2022 17:11:19 GMT

Hi mhusaein

what are the cardinalities of these relationships? Which ones are active and which are in inactive?

Re: create calculated column based on 3 tables

mhusaein — Tue, 22 Nov 2022 17:37:12 GMT

Hi tamerj1 , i updated table 2 , doesn't contain an id column now.

also added more details about the tables relations in the original post

Re: create calculated column based on 3 tables

tamerj1 — Tue, 22 Nov 2022 18:23:05 GMT

mhusaein

Please try

CalculatedColumn =
SUMX (
FILTER (
CALCULATETABLE ( table1, CROSSFILTER ( table2[type], table3[type], BOTH ) ),
table1[id] = table3[id]
),
table1[value]
)

Re: create calculated column based on 3 tables

mhusaein — Wed, 23 Nov 2022 05:49:19 GMT

thanks tamerj1 it worked perfectly