Using All(Filter( with RANKX whilst ignoring specific applied filters

[TFTF_BI]

Hi, 

Im getting my head in a knot about this and can not understand how I build an almost dynamic measure to do the below:

This is the measure I have at this current time: 

Rank_Measure =
VAR RankResult =
    RANKX (
        FILTER (
            ALL ( Students_Table ),
            Students_Table[school_id] = MAX ( Students_Table[school_id] ) -- This section forces the rank to only rank within the same schools
        ),
        CALCULATE ( [Sumx Beh Points] ), 
        ,
        DESC,
        DENSE
    )
RETURN
    IF ( [First_School_Name] <> BLANK (), RankResult, BLANK () ) -- This cleans up the table and removes students that are not included in the filters applied

This measure used the 'Sumx Beh Points' measure as below:

Sumx Beh Points = SUMX(RELATEDTABLE(Students_Table),SUM(Behaviour[points]))

My output is as following: 

Student_Name	School_ID	School_Name	Group	Sumx Beh Points	Rank_Measure
Craig L	61221	Barn Isle	G1	3	2
George R	61221	Barn Isle	G1	7	1
David C	23224	Cape Town	G3	6	2
Lewis H	61221	Barn Isle	G2	1	4
James H	23224	Cape Town	G3	2	4
Max V	23224	Cape Town	G3	4	3
Michael S	61221	Barn Isle	G2	2	3
Mark B	23224	Cape Town	G4	1	5
Dan E	23224	Cape Town	G4	9	1

As you can see the Ranks measure has successfully ranked each student by Sumx Beh Points within their school. So David is rank 1 for Cape Town and George is Rank 1 for Barn Isle. 

My problem... 

When I add a slicer that filters this table to Group 3 the ranking forgets about the rest of the school and only ranks by the ammount of students remaining in the filtered table as shown below: 

Student_Name	School_ID	School_Name	Group	Sumx Beh Points	Rank_Measure
James H	23224	Cape Town	G3	2	2
Max V	23224	Cape Town	G3	4	3
David C	23224	Cape Town	G3	6	1

 As you can see the Rank_Measure is now only ranking the group we've selected and not the rest of the school. I need to be able to apply the group slicer and still see where each of the students rank within the school they are in. 

Please see desired table below: 

Student_Name	School_ID	School_Name	Group	Sumx Beh Points	Rank_Measure
James H	23224	Cape Town	G3	2	4
Max V	23224	Cape Town	G3	4	3
David C	23224	Cape Town	G3	6	2

Important: The groups are listed in the 'Groups' Table under the column called 'Group_Name'. 

If some one would kindly point me in the right direction on how to approach this, that would be amazing! 

Many thanks, Craig.  

[Anonymous]

Hi @TFTF_BI ,

Please refer to my pbix file to see if it helps you.

Rank_Measuremeasure = 
VAR RankResult =
    RANKX (
        FILTER (
            ALL ( 'Table' ),
           'Table'[School_ID] = SELECTEDVALUE('Table'[School_ID]) -- This section forces the rank to only rank within the same schools
        ),
        CALCULATE ( [Sumx Beh Pointsmeasure]), 
        ,
        DESC,
        DENSE
    )
RETURN
   RankResult

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output with more details.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TFTF_BI · Accepted solution]

Thanks for your response,

I like the use of selected value that makes more sense to use rather than Max. Unfortunately this didnt fix the issue, I believed it worked on you PBIX due to having the groups within the same table as the student ids. My workbook has the groups in the groups table and uses the student as the relationship. 

The reason why yours works vs mine is because the Filter(All(schoolID etc locks down the student table for when any other slicers or filters are applied. However if you filter from another table it will still be able to filter the result; in this case when I apply the groups filter from the groups table it is still filtering the rank. 

I have been working on a solution, I have managed to get this to work by using the below: 

RankMeasure = 
VAR Rankresult = RANKX (
    FILTER( ALL ( Students ), 
    Students[school_id] = SELECTEDVALUE(Schools[school_id])), -- This Locks the filtering on the students table and forces the ranking to work per a school id and not collectively. 
    CALCULATE([Sumx Beh Points] , 
    ALL(Groups[group_name])) -- The All( section within calculate allows us to choose which column / filter this measure ignores. In this example if you filter to a group it will still show the ranking of the enitre school. 
    ,
    ,DESC
    ,DENSE
)

I added 'All(Groups(Group) to the calculate function when applying the value to rank. This means its now excluding the groups slicer from effecting the rank output. This is great. The only issue this approach has created is that in the students table I have columns that I would like the the rank to be filtered by, for example: Free dinners, If I want to apply a filter to the measure within the students table to only students that have free dinners the measure doesnt change the output because we used Filter(All(Students) at the begining of the measure which locks the measure from anything being changed in students table. 

To get around this, I have duplicated the students table and used the duplicated table as a filtering table. 

This is now working great and the only part of this measure that is not dynamic is having to manualy enter which filters I wish to ignore within the Calculate section. 

-- If you are using this approach (Duplicating the table to filter) ensure the relationship is set to single direction many to many, otherwise the filters you apply from the newly duplicated table wont have an effect. (Relationship should be Duplicated table to original table. Remember to direct your RLS into this if you use this approach. 

Thanks for your help, I hope this makes sense.