Forum Discussion

swaroopkumarmg's avatar
swaroopkumarmg
Icon for Helper II rankHelper II
7 years ago
Solved

SUMIFS in DAX

Hello there, I'm trying to replicate a function- SUMIFS from excel to Power BI.    I have the following Tables and i need to sum the Hours (Total Hours) based on the criterias.   Table 1: St...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    If you want the calculated column to be in Table 1, the tables need to be connected by a relationship. I would recommend you create a unique key for each stage and band combination so you can connect the tables.

    Use the following as a calculated column in both tables.

    StageBandKey =
CONCATENATE ( Table1[Stage Code], Table1[Band] )

    StageBandKey =
    CONCATENATE ( Table2[Stage Code], Table2[Band] )

    Then connect the tables via the relationship view using the shared key.

    You'll then be able to use the previous code in a calculated column in Table 1 to get the hours for each stage - band combination.

swaroopkumarmg's avatar
swaroopkumarmg
Icon for Helper II rankHelper II
7 years ago

Thanks a ton for your reply and suggestion.

 

I tried this, it did not give any error, but the Hours Column in the Table 1 is with blank values

 

Any suggestion?

 

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

If you want the calculated column to be in Table 1, the tables need to be connected by a relationship. I would recommend you create a unique key for each stage and band combination so you can connect the tables.

Use the following as a calculated column in both tables.

StageBandKey =
CONCATENATE ( Table1[Stage Code], Table1[Band] )

StageBandKey =
CONCATENATE ( Table2[Stage Code], Table2[Band] )

Then connect the tables via the relationship view using the shared key.

You'll then be able to use the previous code in a calculated column in Table 1 to get the hours for each stage - band combination.

  • swaroopkumarmg's avatar
    swaroopkumarmg
    Icon for Helper II rankHelper II
    7 years ago

    That worked like a charm. 

     

    thanks a ton!! :)