Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Using RANKX with Filtered Column

So I'm using the RANKX function to rank my networks by my Incentive SAIFI and it works perfect when I'm showing all networks. However when I apply a filter the rank start over. I'm want the networks to keep the same RANK. My latest DAX and example is below. 

 

 

 

Network Rank = 
var networks = ALLSELECTED(Hiearchy[Network Name])
return RANKX(ALL(networks), [Incentive SAIFI],,ASC)

 

 

 

 Raw Table

Group Network Incentive SAIFI Rank (Measure)

1A12.312
1B12.514
1C10.39
1D13.316
2E5.93
2F7.75
2G9.97
2H8.96
3I1111
3J3.91
3K12.815
3L6.74
4M5.72
4N10.810
4O10.28
4P12.413

 

Expected When Filtering Group 2

Group Network Incentive SAIFI Rank (Measure)

2E5.93
2F7.75
2G9.97
2H8.96

 

What I'm actually getting (with a previous measure, not the one above, I've been trying so many things)

Group Network Incentive SAIFI Rank (Measure)

2E5.91
2F7.72
2G9.94
2H8.93

 

Thanks in advance for the help.

 
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    That didn't work for me and still the table is much bigger than I'm letting on. I don't want to just post the full raw table with company info. 

    I would have loved to have gotten this working with a measure but I gave up and created a calculated table. Below was my calculated table. 

    Network Rank = 
UNION(
    SUMMARIZE(CALCULATETABLE(VALUES(Hiearchy[Network Name]), Hiearchy[Group] = "Group1", Hiearchy[Network Name] <> BLANK()), Hiearchy[Network Name], "Rank", RANKX(ALLSELECTED(Hiearchy[Network Name]), [Incentive SAIFI],,ASC,Skip)),
    SUMMARIZE(CALCULATETABLE(VALUES(Hiearchy[Network Name]), Hiearchy[Group] = "Group2", Hiearchy[Network Name] <> BLANK()), Hiearchy[Network Name], "Rank", RANKX(ALLSELECTED(Hiearchy[Network Name]), [Incentive SAIFI],,ASC,Skip)),
    SUMMARIZE(CALCULATETABLE(VALUES(Hiearchy[Network Name]), Hiearchy[Group] = "Group3", Hiearchy[Network Name] <> BLANK()), Hiearchy[Network Name], "Rank", RANKX(ALLSELECTED(Hiearchy[Network Name]), [Incentive SAIFI],,ASC,Skip)),
    SUMMARIZE(CALCULATETABLE(VALUES(Hiearchy[Network Name]), Hiearchy[Group] = "Group4", Hiearchy[Network Name] <> BLANK()), Hiearchy[Network Name], "Rank", RANKX(ALLSELECTED(Hiearchy[Network Name]), [Incentive SAIFI],,ASC,Skip)),
    SUMMARIZE(CALCULATETABLE(VALUES(Hiearchy[Network Name]), Hiearchy[Group] = "Group5", Hiearchy[Network Name] <> BLANK()), Hiearchy[Network Name], "Rank", RANKX(ALLSELECTED(Hiearchy[Network Name]), [Incentive SAIFI],,ASC,Skip))
)

     

8 Replies