Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Power BI SUMIFS on another table

Hello everybody

 

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

 

Table1 : 

 

ABC
Peter10Daughter
Franck20Son
Mark30Daughter

 

Table2 :

 

DEF
PeterPeter_Son100
PeterPeter_Daughter450
FranckFranck_Son600

 

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 !

  • Anonymous's avatar
    Anonymous
    4 years ago

    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

3 Replies

  • AUaero's avatar
    AUaero
    Icon for Responsive Resident rankResponsive Resident
    4 years ago

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

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        Anonymous
        Not applicable
        4 years ago

        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