DAX SUM IF where 2 Columns in 2 different Tables Match

[j0nthn]

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]

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]

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

KEY TABLE (all unique values)

Key	Revenue
1	x
2	x

<tbody>
</tbody>

Data table (might contain duplicate values)

Key	Revenue
1	6
2	6

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

=CALCULATE(sum(datatable[revenue])

 

[j0nthn]

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]

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