# DAX SUM IF where 2 Columns in 2 different Tables Match



## j0nthn (Jan 19, 2016)

Hi,

Is there a formula that let's me SUM a value (i.e. 'Data'[Clicks]) where two columns in two different tables match (i.e. 'Data'[Week] = 'Landing Page'[Week] AND 'Data'[Page] = 'Landing Page'[Landing Page] )

I've been looking at different combinations of SUM, SUMX, FILTER and ALL, but DAX seems to be very limited in this aspect, whereas I would be easily able to do this using a regular excel formula..

Thanks in advance,
Jon


----------



## Matt Allington (Jan 22, 2016)

j0nthn said:


> but DAX seems to be very limited in this aspect



DAX is very powerful in this space. 


 You don't really provide enough info here for me to help you.  Are you wanting a measure or a calc column?  Are the other tables lookup tables or data tables?  Maybe post a sample workbook.


----------



## j0nthn (Jan 23, 2016)

I have two tables, that look like this, that are linked through the KEY field (one-to-many relationship)

KEY TABLE (all unique values)

KeyRevenue1x2x

<tbody>

</tbody>
Data table (might contain duplicate values)

KeyRevenue1626

<tbody>

</tbody>
The 'Key' fields in both tables are linked through a relationship, I can use the RELATED function in the 'Data table' to find values, but it doesn't work in the KEY table. Do you know how to fill in the x's in the Key table?

Best,
Jon


----------



## Matt Allington (Jan 25, 2016)

=CALCULATE(sum(datatable[revenue])


----------



## j0nthn (Jan 25, 2016)

I cannot believe it's this simple.. I tried that but with a USERELATIONSHIP function behind it
Thank you so much,
Best
Jon


----------



## Matt Allington (Jan 25, 2016)

Well it looks simple, but there is a lot to learn to truly understand how power pivot works - this formula included. To understand this formula, you really need to understand row context, filter context, context transition and filter propagation. Once you have the principles down, it is obviously easier to move forward. I wrote my book to help Excel users get started with Power Pivot if you are interested. 
Learn to Write DAX - the book for all Excel users


----------

