Hi Mynda
I've been trying to get my head round inner and outer row contexts and table functions in DAX. A colleague created a number of calculated columns and then tried explaining it to me via a COUNTIFS scenario. Please see the attached file.
The COUNTIFS looks to see if the first two conditions are met, products and date, then it looks to see if that set of products or dates contains an AC type for example for that particular row. What throws me is that a count of 1 is returned even when you get to the second row of the data even though that row is type XX.
I understand that the set PHC 3408 and date 01/01/22 has three rows and one of those rows has a type AC. In this case the outer row context in DAX speak is the AC agreement but I still can't quite make the connection. Can you provide any similar COUNTIFS examples in Excel or further reading on the topic? Thank you.
Tim
Hi Tim,
Taking AC count as an example:
=COUNTIFS($C:$C,C4,$D:$D,D4,$E:$E,"AC")
In English the formula in cell F4 says, count the number of records that have product code 3408 for date 1/1/22 and are type AC.
The type, 'AC' is hard keyed in the formula, so it doesn't matter what row the formula is on as it will always be counting how many times AC is in column E for the PC and Date on the current row, in this case row 4.
In other words, it only cares what the row context is for the PC and Date because the Type will be AC for every row because it's reading the type from the hard keyed value, not the row context.
Hope that makes sense.
Mynda
