Can we create a calculated table and do aggregation out of two tables ?

[brickanalyst]

Hello everyone,

suppose we have two tables same format (please see below)

and 

we have these two tables we don't have third table so can we create a table out of these two and make calculations for instance

and does these three have to have a relationship or not ?

ask me any questions if there's
thank you.

[Anonymous]

Hi @brickanalyst ,

The Table data is shown below:

Regarding your question, if you intend to achieve your goals through measure, please refer to the following steps.

1.Use the following DAX expression to create a table

Table = SUMMARIZE('Table1',[project_id],[project_name])

 2.Creating table-to-table relationships

3.Use the following DAX expression to create a measure

_S_A_1 = ABS(MAX('Table1'[S_A_1]) - MAX('Table2'[S_A_1]))

4.Final output

If you intend to create a calculation table, refer to the following dax expression.

Table3 = 
VAR _table = UNION('Table1','Table2')
VAR _result = SUMMARIZE(_table,[project_id],[project_name],
"S_A_1", 
ABS(
MAXX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_1]) - 
MINX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_1])
),
"S_A_2", 
ABS(
MAXX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_2]) - 
MINX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_2])
),
"S_A_3", 
ABS(
MAXX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_3]) - 
MINX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_3])
),
"S_A_4", 
ABS(
MAXX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_4]) - 
MINX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_4])
),
"S_A_5", 
ABS(
MAXX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_5]) - 
MINX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_5])
)
)
RETURN _result

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[brickanalyst]

@Anonymous 

Hello Zhou,

I really appreciate it for quick response, two things here
1) I shared a very basic example of what I'm working on right now, so I actually don't have same amount of columns that's why union doesn't work. Can we still do that? with inner join like t-sql and grab the columns we want? 

2) is there a big change if we calculate like this ('y1'!C4 - 'r1'!C4) / 'r1'!C4 ? and we have one unique common column to create relationship

please ask me if it's not clear to picture
thanks

[Anonymous · Accepted solution]

Hi @brickanalyst ,

1.Regarding your first question, you mentioned that the two tables do not have the same number of columns which prevents the 'UNION' function from being used.

Something like this?

Please use the 'Append' operation in 'Power query'.

Replaces null values ​​in columns with 0 for calculations.

DAX is mainly used to analyze data, rather than to get the data you want like SQL. Maybe 'LOOKUPVALUE' can meet your needs.

LOOKUPVALUE function (DAX) - DAX | Microsoft Learn

2.Regarding your second question, if you change the calculation method, you only need to change the DAX expression part.

Try this.

Table4 = 
VAR _table = 'Table3'
VAR _result = SUMMARIZE(_table,[project_id],[project_name],
"S_A_1", 
DIVIDE(
MAXX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_1]) ,
MINX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_1]) , 0
),
"S_A_2", 
DIVIDE(
MAXX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_2]) ,
MINX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_2]) , 0
),
"S_A_3", 
DIVIDE(
MAXX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_3]) , 
MINX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_3]) , 0
),
"S_A_4", 
DIVIDE(
MAXX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_4]) , 
MINX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_4]) , 0
),
"S_A_5", 
DIVIDE(
MAXX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_5]) , 
MINX(FILTER(_table,[project_id] = EARLIER([project_id]) && [project_name] = EARLIER([project_name])),[S_A_5]) , 0
)
)
RETURN _result

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.