Forum Discussion

Sjoerd_g's avatar
Sjoerd_g
Frequent Visitor
4 years ago
Solved

Complex RANKX with multiple slicers/filters

Hi all,   I am stuck on a RANKX DAX formula and getting very strange results. There are multiple things at play that make the RANKX a bit complex. I created a sample file, but the database has more...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, Sjoerd_g 

     

    Your model is messy and so are the requirements, it's hard for me to give specific opinions.

    For your idea, the reference I can give is that you need to use summarize() in rank() function to customize a context you want, and then sort it. 

    If you don't want the line with response < 15 to appear on the visual, you can filter in the filter pane like this:

    Measure =
RANKX (
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( sample3 ),
            [Benchmarking],
            [company],
            "Responses", DISTINCTCOUNT ( sample3[response id] ),
            "NPSSOCRE", CALCULATE ( AVERAGE ( sample3[NPS Calc] ), sample3[nps_score] <> BLANK () )
        ),
        [Responses] > 15
    ),
    [NPS score],
    ,
    ,
    DENSE
)

     

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

    Best Regards,
    Community Support Team _ Janey

     

davehus's avatar
davehus
Memorable Member
4 years ago

Hi Sjoerd_g, we can work on that bit.

A calculated column with an allexcept will sum total responses and you can use it as a slicer or in the measure. Just need to test. ğŸ˜Š Just in terms of the company, is it based on total nps. Send me desired result in an excel doc. Even a snip will do. 

Sjoerd_g's avatar
Sjoerd_g
Frequent Visitor
4 years ago

Cool thanks davehus 

 

Yes, the NPS is calculated at the company level (but can differ per segment because not all scores for a company will sit in the same segment).

 

What I would expect to see if we slice by Segm3 and filter out responses < 15 is for company BB to be ranked 1 (and the only company in this sample file)

 

Or if we slice by Segm2 and filter responses <= 15 then we would see company BB and CC on a shared first place:

 

I tried the ALLEXCEPT but could not get the DISTINCTCOUNT in there (or [Response #] measure)

 

Ps. keeping in mind the Response count is further filtered by Channel as not all are NPS responses. In the sample file the Channel = BLANK. For instance, in Segm3 we will have company AA with 13 responses when filtered by Channel = BLANK, but 14 responses if we do not take this into account, so if we would have [responses #] < 14 it would still show as:

But then if we apply the Channel filter is blank then it should result in BB being 1st. 

 

I'm sorry if I'm not making any sense... This one is doing my head in haha.

 

 

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    Hi, Sjoerd_g 

     

    Your model is messy and so are the requirements, it's hard for me to give specific opinions.

    For your idea, the reference I can give is that you need to use summarize() in rank() function to customize a context you want, and then sort it. 

    If you don't want the line with response < 15 to appear on the visual, you can filter in the filter pane like this:

    Measure =
RANKX (
    FILTER (
        SUMMARIZE (
            ALLSELECTED ( sample3 ),
            [Benchmarking],
            [company],
            "Responses", DISTINCTCOUNT ( sample3[response id] ),
            "NPSSOCRE", CALCULATE ( AVERAGE ( sample3[NPS Calc] ), sample3[nps_score] <> BLANK () )
        ),
        [Responses] > 15
    ),
    [NPS score],
    ,
    ,
    DENSE
)

     

    Did I answer your question? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

    Best Regards,
    Community Support Team _ Janey

     

    • Sjoerd_g's avatar
      Sjoerd_g
      Frequent Visitor
      4 years ago

      Hi Janey,

       

      Thank you so much for looking into this and providing me with your solution. I'm having a bit of a crazy week but will try to implement this as soon as I can and get back to you.

       

      Oh, yes sorry that the sample file is a bit of a mess. I promise the actual dataset and report is much better. 😉

       

      Thanks!

    • Sjoerd_g's avatar
      Sjoerd_g
      Frequent Visitor
      4 years ago

      I have implemented it into my model and works beautifully. Thank you very much Janey!