Ranking based on +/- from variable using DAX

rbreneman sorry for the late reply, this is what you need to change in your measure:

 

VAR RankedEnrollmentTbl = 
    ADDCOLUMNS (
        vEnrollmentTbl, 
        "Rank", RANKX ( vEnrollmentTbl, ABS ( [Enrollment] - RefSchoolEnrollment ), , ASC ), 
        "Distance",  ABS ( [Enrollment] - RefSchoolEnrollment ) 
    )

 

and here is the full measure:

Calculations = 
//Reference School 
VAR RefSchoolEnrollment = [ReferenceSchoolEnrollment]

// Set Variables for enrollment filter range

VAR Enrollment_SeriesStart = RefSchoolEnrollment + ROUND( ( RefSchoolEnrollment * Criteria[CriteriaMin] ), 0 )
VAR Enrollment_SeriesEnd = RefSchoolEnrollment + ROUND( ( RefSchoolEnrollment * Criteria[CriteriaMax] ), 0 )
VAR EnrollmentCriteriaRange = GENERATESERIES(Enrollment_SeriesStart, Enrollment_SeriesEnd)

// Create virtual table that is filtered to only contain schools based on above enrollment filtering. In production this will also contain other filters
VAR vEnrollmentTbl = CALCULATETABLE(tblEnrollment,tblEnrollment[Enrollment] IN EnrollmentCriteriaRange)

// Create virtual table that is same as above but adds rank column
        // RANKX ( CALCULATETABLE(ALL(tblEnrollment),tblEnrollment[Enrollment] IN EnrollmentCriteriaRange), ABS ( [Enrollment] - 

VAR RankedEnrollmentTbl = 
    ADDCOLUMNS (
        vEnrollmentTbl, 
        "Rank", RANKX ( vEnrollmentTbl, ABS ( [Enrollment] - RefSchoolEnrollment ), , ASC ), 
        "Distance",  ABS ( [Enrollment] - RefSchoolEnrollment ) 
    )
//string
VAR DebugOUtput = CONCATENATEX ( RankedEnrollmentTbl, [Enrollment] & "-" & [Distance]  & "-" & [Rank], ",", [Enrollment] )
// School 1
VAR S1 = FILTER(RankedEnrollmentTbl,[Rank] = 1 )
VAR S1_Enrollment = MAXX(S1,[Enrollment])
VAR S1_ID = MAXX(S1,[ID])
// School 2
VAR S2 = FILTER(RankedEnrollmentTbl,[Rank] = 2 )
VAR S2_Enrollment = MAXX(S2,[Enrollment])
VAR S2_ID = MAXX(S2,[ID])
// School 3
VAR S3 = FILTER(RankedEnrollmentTbl,[Rank] = 3 )
VAR S3_Enrollment = MAXX(S3,[Enrollment])
VAR S3_ID = MAXX(S3,[ID])

VAR Solution = 
SWITCH( SELECTEDVALUE( Output[School] ),
    "Selected School",
        SWITCH( SELECTEDVALUE( Output[Name] ),
        "Enrollment",[ReferenceSchoolEnrollment],
        "School Name","Sample School"
        ),
    "Similar School #1",
        SWITCH( SELECTEDVALUE( Output[Name] ),
        "Enrollment",S1_Enrollment,
        "ID",S1_ID
        ),
    "Similar School #2",
        SWITCH( SELECTEDVALUE( Output[Name] ),
        "Enrollment",S2_Enrollment,
        "ID",S2_ID
        ),    
    "Similar School #3",
        SWITCH( SELECTEDVALUE( Output[Name] ),
        "Enrollment",S3_Enrollment,
        "ID",S3_ID
        )
)

RETURN
Solution

 

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

 

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.