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]))

     

vanessafvg's avatar
vanessafvg
Community Champion
9 years ago

i dont think your data is modelled correctly.  Firstly how are the joined together?  you need some kind of unique id that flows through to all tables ie. both project and user s should have a project id?  or is that link there?

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?

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    9 years ago

    ah ok makes sense, so what error are you getting?  

  • dedelman_clng's avatar
    dedelman_clng
    Community 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]))