Power BI SUMIFS on another table

[Anonymous]

Hello everybody

I have a maybe easy question but I dont find the way to do it easily : 

Table1 : 

A	B	C
Peter	10	Daughter
Franck	20	Son
Mark	30	Daughter

Table2 :

D	E	F
Peter	Peter_Son	100
Peter	Peter_Daughter	450
Franck	Franck_Son	600

I woud like : a sumifs + concatenate => SUMIFS(Table2[ColonneF], Column_E = (Column_A & Column_C) ) )  onthe table1

I dont have a direct unique key between the two tables that is why the join is a little bit complicated.

Kind regards, thanks !

[AUaero]

Create a calculated column on Table1 to create a join to Table2:

[Anonymous]

Thank you, I thought about it but I would like to do it in DAX.

I dont find the equivalent of MAX for Double, you know when you make column_values = MAX(colum_values_2) 

I am looking the equivalent for a String column like

CALCULATE( SUM(table_2[column_F],

table_2[column_D] = table_1[column_A])

or 

table_2[column_E] = table_1[column_A] & "_" & table_1[column_C]))

without joining the two tables, just a kind of sumIF

Kind regards

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can create a calculated column as below in Table1:

Column = 
CALCULATE (
    SUM ( 'Table2'[F] ),
    FILTER ( 'Table2', 'Table2'[E] = 'Table1'[A] & "_" & 'Table1'[C] )
)

Best Regards