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 ...
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))
)

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Network Rank = 
var networks = ALLSELECTED(Hiearchy[Network Name])
return RANKX(ALL(networks), [Incentive SAIFI],,ASC)

 

The DAX query above gives me this error message.

 

v-chuncz-msft's avatar
v-chuncz-msft
Community Support
6 years ago

Anonymous 

 

You may try the measure below.

Measure =
RANKX (
    ALL ( Hiearchy[Group], Hiearchy[Network Name] ),
    [Incentive SAIFI],
    ,
    ASC
)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    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))
)