SUMIF Equivalent in DAX

[navarrobr]

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 Hours when the Project Company is different from the User company. I tried this:

Intercompany Hours = CALCULATE (Sum(Hour[Hours]);Project[Company]<>User[Company])

It does not work... I would appreciate any help!

[vanessafvg]

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]

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]

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]

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 · Accepted solution]

Now it worked!

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

[vanessafvg]

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