Remember last time when we needed to keep track of certifications in a fictional company? We did the preliminary steps to create the base table for the table visual below in this blog post and we’ve come to the next hurdle: the column called “current”.
If only we were using Excel
Finally we can tackle the last hurdle – the column that shows both the current number of certifications and the requested goal. Had this been Excel it would have been dead easy – we just create a new cell that concatenates two other cells like this:
Then we copy the formula to all the rows. Easy. But this is not Excel. The “goal” part is simple – that’s just another column. The trick is to count all the other rows in the table with the same key. Let’s add the key column to the table so we see what we’re working with. “CompKey” is the concatenated key we created in the previous blog post. “Number of certs” is a count of the rows in the table, and because of row context it gets evaluated per key.
Manual DAX for automatic behavior
We need to duplicate this automatic behavior of Power BI Desktop in DAX, and create a nested row context that COUNTROWS can iterate over in order to give us a count of the number of rows per key. This can be done using the function EARLIER. This function is very powerful but can be a handful to get to grips with. From the documentation:
“EARLIER is useful for nested calculations where you want to use a certain value as an input and produce calculations based on that input. In Microsoft Excel, you can do such calculations only within the context of the current row; however, in DAX you can store the value of the input and then make calculation using data from the entire table.”
That sounds exactly like the problem we have before us. Using the example in the documentation and tweaking it a bit we get this measure:
Current = COUNTROWS(FILTER(MergedDataTable, EARLIER(MergedDataTable[CompKey]) = MergedDataTable[CompKey]))
Behind the scenes, the calculation steps look like this:
- The EARLIER function gets the CompKey for the current row (West;MCSA:Cloud Platform”). As we’ve just started execution, this will be the first row in the table.
- The FILTER function will iterate over all the rows in the table and return a table with rows having the same value for CompKey as in step 1.
- The COUNTROWS function counts the rows of the filtered table from step 2.
- The calculated column formula moves to the next row and repeats all the steps until the end of the table is reached.
We have now successfully duplicated the behavior of Power BI Desktop automatic count. Put it into the table and it will look like this:
All we need to do to complete the mission is to change the measure to concatenate the value from the other column like this:
Current = COUNTROWS(FILTER(MergedDataTable, EARLIER(MergedDataTable[CompKey]) = MergedDataTable[CompKey])) & "(" & (MergedDataTable[Goal] & ")")
Let’s hide the concatenated key from earlier et voíla. We’re back to where we started.