Forum Discussion

navarrobr's avatar
navarrobr
Frequent Visitor
9 years ago
Solved

SUMIF Equivalent in DAX

Hi everyone!   I have a simple situation here, but can't figure out how to solve it. Imagine the following data model:     What I need is a measure (or maybe a column?) that shows the sum of...
Need Help
  • navarrobr's avatar
    navarrobr
    9 years ago

    Now it worked! :smileyhappy:

     

    Intercompany Hours = SUMX(filter('Hour';related(Project[Project_company])<>related(User[User_company]));sum('Hour'[Hours]))

     

navarrobr's avatar
navarrobr
Frequent Visitor
9 years ago

Hi, thanks for answering!

 

The Hour table is my Fact Table, it has foreign keys from my two Dimension Tables (Project and User) and one measure field (hours). Is that clearer now?

dedelman_clng's avatar
dedelman_clng
Icon for Community Champion rankCommunity Champion
9 years ago

I would think you would want to change to SUMX and use RELATED

 

CALCULATE( 
   SUMX (Hours, Hours[Hours]),
   RELATED(Project[Company]) <> RELATED(User[Company]) 
)

 

Hope this helps,

David 

  • navarrobr's avatar
    navarrobr
    Frequent Visitor
    9 years ago

    Hi David and Vanessa,

     

    Here is a link to my example file: http://www.syscope.com.br/files/example.zip

     

     

    Here is the data model:

     

    Here is the data, my expression should show the 10 hours below (where the user company is different from the project company):

     

     

    This is the error I got with my formula:

     

    And David, this is the error I got from your formula:

     

     

    • navarrobr's avatar
      navarrobr
      Frequent Visitor
      9 years ago

      Now it worked! :smileyhappy:

       

      Intercompany Hours = SUMX(filter('Hour';related(Project[Project_company])<>related(User[User_company]));sum('Hour'[Hours]))