topic Re: Rank Output based on Selected Criteria in DAX Commands and Tips

Rank Output based on Selected Criteria

Toni_H — Wed, 25 May 2022 00:10:06 GMT

Hello,

Essentially I need to figure out a way to see a list of students based on best fit to my selected search criteria.
Example:
A user chooses 5 criteria for search (range between 1-10), any students who match at least one criteria will be displayed, with the students matching the most search criteria displayed at the top and sorted in descending order (i.e. students who match all 5 criteria would be displayed at top, descending down to students who only meet one criteria). The students should have a percentage match displayed (e.g. students matching all 5 criteria would display 100%, students who match 4 criteria would display 80%, down to students who only match one criteria displaying 20%). If more than one student has the same exact percentage match, the students should be displayed in A-Z alpha ordering.

I was thinking that a switch statement could work, but my attempts have not been successful.
The slicers will be on separate pages because ultimately they could select from 28 criteria.

Thanks so much!

Re: Rank Output based on Selected Criteria

amitchandak — Wed, 25 May 2022 01:53:12 GMT

Toni_H , First create a measure on match criteria

new measure =
var _max = maxx(allselected(criteria1),criteria1[Date])
var _min = minx(allselected(criteria1),criteria1[Date])
return
calculate( sum(Table[Value]), filter('Table', 'Table'[attitude] >=_min && 'Table'[attitude] <=_max))

add other too

then create a measure

Rankx(allselected(Table[Name]),[new measure],,desc,dense)

Re: Rank Output based on Selected Criteria

CNENFRNL — Wed, 25 May 2022 03:43:35 GMT

Re: Rank Output based on Selected Criteria

Toni_H — Wed, 25 May 2022 15:51:04 GMT

Thank you so much for taking the time to provide the pbix file. I was having issues sending mine as a link.

I am hopeful that this will work. However, is there a way to make the denominator dynamic? Sometimes they will select from 2 slicers other times 5 or 10?

Thanks again.

Re: Rank Output based on Selected Criteria

Toni_H — Wed, 25 May 2022 15:56:59 GMT

Thank you for taking the time to respond.
This does work, but I would need to create a rank measure that considers all the criteria selected in the slicers.

Not sure how that would work...

Thanks again for your help,