Rank with filter

[lee_hawthorn]

I have table :

Sales

ProductCat    ProductGroup   Sales

1                    1                        100

2                    1                        105

3                    2                         34

4                    2                         23

5                    5                        1000

I need to filter out Product Cat 5 and then add a rank column, partitioned by  ProductGroup i.e. :

ProductCat    ProductGroup   Sales   Rank

1                    1                        100      2

2                    1                        105      1

3                    2                         34       1

4                    2                         23       2 

I can do the rank with :

ADDCOLUMNS('Sales', "Rank",  RANKX(CALCULATETABLE('Sales', ALLEXCEPT('Sales', 'Sales[ProductGroup]), 'Sales'[Sales])

One thing I'm struggling with filtering out ProductCat 5 before running the RANKX.   I tried adding it into CALCULATETABLE as an additional filter but this has no effect due to the ALLEXCEPT.  I've tried a nested CALCULATETABLE with the filter but this has no effect either.

Any ideas how I can apply a filter prior to CALCULATETABLE?

[amitchandak]

@lee_hawthorn ,

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[AllisonKennedy · Accepted solution]

@lee_hawthorn 

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. 

https://sites.google.com/site/allisonkennedycv

[mahoneypat]

Just 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

[lee_hawthorn]

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/