Forum Discussion
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 than 3m rows for 4 months of data. The dataset is for responses to a survey and contains responses from many different companies and branches, which can sit within 19 different segments (at the branch level).
I want to achieve a few things:
Show table with company ranking sliced by Segment and Date, and on top of that, we need to be able to exclude companies with low responses. This may be hardcoded in the DAX, but if possible, with slicer/filter, even better.
Secondly, a table that shows the company's rank within a segment, the number of companies within the segment, and the maximum and minimum scores within the segment. -- I have not even started solving this problem yet as I need to have the NPS ranking first, but this is the ultimate goal.
I tried many different combinations with RANKX, firstly the straightforward:
But the rankings are all over the place (except when the score is 100), and I just can't figure out how to properly write it, so it takes everything into account. The things that make it more complex are:
- In the dataset, there are entries without a nps_score as there are several channels, and not all are survey responses. In the [NPS Calc] measure, I accounted for this by filtering "nps_score <> BLANK()" and added it as a filter on the visual.
- Branches within a Company can sit in different Segments, so the Company NPS can be different per Segment.
- The outside filters need to be accounted for, most importantly, Date and Segment.
- Companies with low responses should be excluded from the ranking (I.e. [Response #] >= 10).
- To accomplish this I was thinking of an IF statement like:
- IF(CALCULATE(DISTINCTCOUNT(Sample3[response id]),FILTER(Sample3, DISTINCTCOUNT(Sample3[response id]) >= 10)) = BLANK(),"", ...
The sample file is available here: https://we.tl/t-jo210iYADN
I would be grateful if someone could point me in the right direction as I have been stuck for a while and getting more confused by the minute. Thanks!
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
7 Replies
Hey Dave, thanks for this. This does indeed work in a way but it won't account for the number of responses.
I tried to get around this by an IF statement but then it just replaces the rank with a blank:
Rank By Segment =IF(CALCULATE(DISTINCTCOUNT(Sample3[response id]),FILTER(Sample3, DISTINCTCOUNT(Sample3[response id]) <= 15)),"",RANKX(ALL(sample3[company]),[NPS score]))Nor does:
Rank By Segment =CALCULATE(RANKX(ALL(sample3[company]),[NPS score]),FILTER(sample3,DISTINCTCOUNT(sample3[response id]) >= 15))
