Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Toni_H
Frequent Visitor

Rank Output based on Selected Criteria

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.

Toni_H_0-1653437321097.png

 


Thanks so much!

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

CNENFRNL_0-1653450171163.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

4 REPLIES 4
CNENFRNL
Community Champion
Community Champion

CNENFRNL_0-1653450171163.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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.

TH

amitchandak
Super User
Super User

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

 

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,

TH

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.