Hi, so I have a beast mode calculation in one of my datasets that looks like this:
CASE
WHEN SUM(SUM(1)) OVER (PARTITION BY TRACK: Title,LIBRARY: Name,TRACK: Version) > 1 and TRACK: Version = 'Full Mix' THEN 'YES'
WHEN TRACK: Version <> 'Full Mix' THEN null
else 'NO' end
The purpose is to make sure that there aren't duplicate track titles. I use this for metadata management, and with thousands of track titles, it's pretty common that people accidentally name their track something that has been used before. We don't want that. What I have right now halfway works, but the problem is that it only compares to other rows if they're in the selected section that's currently showing on the card. However, when working fast I frequently will filter the current album, meaning it isn't comparing to earlier track titles and will always tell me there aren't any duplicates, even if that isn't the case. I could just check before filtering, but it's an extra step that's bothersome, and with the raw amount of data I have, my computer often lags when I try to scroll all the way to the most recent albums to make sure there aren't duplicates.
So my question is, is there a way to do this same function, but make sure that it's calling to every single row of data whether it's part of the filtered section of the card or not? Is there something I can do in the ETL, perhaps? I haven't thought of anything that works. Comparing column to column is easy, but row to row is difficult. Thanks!
