Forum Discussion
Rank with filter
Easy answer is to try this:
SalesRank = ADDCOLUMNS(FILTER(Sales,Sales[ProductGroup]<>5),"Rank", RANKX(ALLSELECTED(Sales), [TotalSales]))So basically filter the Sales table before you add columns to it.I wonder though, is there a reason you're using DAX ADDCOLUMNS to create a new table? How do you need to use this in the report and should the rank update if you filter out another product category.One thing you can do is use a MEASURE instead. That will enable you to create a table visualization that can have the measure added to it. Then you can filter that visual to exclude category 5 and the rankings will update.
Rank = RANKX(ALLEXCEPT(Sales,Sales[Sales],Sales[ProductGroup]), [TotalSales])Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos.
I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query.
Microsoft EmployeeJust to clarify. Are you trying to make a measure? calculated column? or calculated table? Depending on what your goal, I would suggest a simpler DAX expression to achieve your goal.
FYI that you can just add a term to your Calculatetable( ) expression to filter out ProductCat <> 5 after the Allexcept() clause. Note there seems to be a missing parentheses after that anyway.
Regards,
Pat
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
I'm sticking with table at the moment as there's more processing to do. We can't add filters in CALCULATE as filter context is being amended by allselected().
I will try applying the filter in an outer calculate.
This article explains it all
https://www.sqlbi.com/articles/order-of-evaluation-in-calculate-parameters/