Forum Discussion
TopN + Others - Circular reference
Thanks amitchandak ... I think it's close. I'm still getting an error on the last statement -
if (RankCSTVolume<=10, 'CSTs from 2018 to current'[Cat2andCat3], "Others")
The error is - Cannot find name '[Cat2andCat3]'
Seems like a syntax error but I don't see why. It seems to only accept a caclulated field.
Try like.
if (RankCSTVolume<=10, firstnonblank('CSTs from 2018 to current'[Cat2andCat3],blank()), "Others")
But row context is very important
Check how I have taken care of that in date diff :https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Difference-Across/ba-p/934397#M451
amitchandak thanks for your help. Unfortunately when I replaced the 'if' statement I recieved a circular reference error. I tried to create a sample file to share with you so that you could see the data and error, but apparently this board restricts attachments. I also tried to break up the statements into two statements thinking that might address the circular reference but no luck. Open to any other ideas. Below is the last statement I used.
CSTs TopN+Others = VAR RankCSTVolume = RANKX(ALLselected('CSTs from 2018 to current'),CALCULATE( SUM('CSTs from 2018 to current'[Service Tickets]) ),,DESC) return if (RankCSTVolume<=10, firstnonblank('CSTs from 2018 to current'[Cat2andCat3],blank()), "Others")See if you can replicate your issue on this file
https://www.dropbox.com/s/drp7gscx3w1liyc/sales_analytics_cumm.pbix?dl=0
amitchandak thanks will do. When I used your link I was able to create a drop box account! I'm a newbie so this is all somewhat new to me. Anyhow.. I now have a link that I can share with you too. I tried to insert the link directly into the reply but that didn't work for some reason. Hopefully you can access this. In the meantime, I will try to recreate the issue on your file.
https://www.dropbox.com/s/gd1pgjx82ib98ij/RankTopNandOthers.pbix?dl=0