Forum Discussion
What does TopN and Filter do?
In my experience, "TOPN(1..." is usually a cheat and/or a safety net then the coder ran into a situation where there might be duplicate rows that all met the conditions needed, but the formula itself for whatever reason required only one row. But this doesn't seem to be the case here. This one is weird and I am struggling to think of why this was written this way rather than a much simpler way that I'll get back to in a minute. I kind of think whoever wrote this was just slightly confused, or maybe just read a cool article on how TOPN works and got excited.
The filter isn't doing anything particularly special, just returning some matching conditions. TOPN(1 then returns just a single row from that matching filtered table which has the highest value in the [pixel_count] column. Then it does an average. An average of a single row value. Which is the same as returning that single row's value. The average of the single max value. So why not just use MAX in the first place?
Obviously I don't have access to the data itself so maybe there's something weird about the table structure that changes the results, but just from reading the code I cannot imagine that you would get a different result with this:
Shared Pixel = CALCULATE(
MAX('Insights - Browse Category - All'[pixel_count]),
FILTER(
'Insights - Browse Category - All',
'Insight - Browse Cat All (summarized)'[date]='Insights - Browse Category - All'[date]
&&'Insight - Browse Cat All (summarized)'[pixel]='Insights - Browse Category - All'[pixel]
&&'Insight - Browse Cat All (summarized)'[Device]='Insights - Browse Category - All'[Device]
&&'Insight - Browse Cat All (summarized)'[Category]='Insights - Browse Category - All'[Category]
&& 'Insights - Browse Category - All'[url]=""
)
)
Can you add a new column like that and see if it produces different results? Maybe I'm missing something.
Thanks for the constructive feedback guys. I'll look to see what the additional column will do but you are right on the underlying data, it isn't normalised and is proving a headache to decipher. Cheers