Ranking based on +/- from variable using DAX

[rbreneman]

Hi! Hopefully this is an easy one, I just can't seem to wrap my head around how to make it work.

I'm writing some calculations in DAX. I have a table stored as a variable, looks something like below:

School	Enrollment
SchoolA	1402
SchoolB	1408
SchoolC	1399
SchoolD	1388
SchoolE	1476

I have another variable that I've stored the enrollment number for a reference school. Let's say the reference school enrollment is 1400 and the variable is called Num_Enrolled.

What I need to do is create a variable table that sorts (ranks) my existing variable table by how close the enrollment number is to my reference school. For example, based on the data above, my solution should be this:

School	Enrollment	Rank
SchoolC	1399	1
SchoolA	1402	2
SchoolB	1408	3
SchoolD	1388	4
SchoolE	1476	5

I know I'll need to use DAX something like this to make my ranked table but I can't figure out how to make the rank list in order of how close the enrollment number is to my reference number.

VAR RankedEnrollmentTbl = ADDCOLUMNS(EnrollmentTbl,"Rank",RANKX(EnrollmentTbl,[Enrollment],,DESC,Dense))

Any help would be appreciated!

Thanks!

[parry2k]

@rbreneman glad to hear that you have a solution in place. Cheers!!

✨ 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.

[parry2k]

@rbreneman hmmm try this to break the tie:

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

✨ 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.

[rbreneman]

@parry2k ,
That didn't seem to work, gave me the same result as before. That being said, I think I got it working. This is what I ended up doing. I added + RAND() to the original rank number so that it would have a decimal, but I still needed the output rank column to be an integer (1, 2, 3) so I ran it through another RANKX to create a rank column that is based on the previous rank.

Thanks for all your help on this!!

// Create virtual table that is same as above but adds rank column plus a random decimal number between 0 and 1. This prevents ties
VAR TempRankedEnrollmentTbl = 
    ADDCOLUMNS (
        vEnrollmentTbl, 
        "TempRank", RANKX ( vEnrollmentTbl, ABS ( [Enrollment] - [ReferenceSchoolEnrollment] ), , ASC ) +
                        RAND(), 
        "Distance",  ABS ( [Enrollment] - [ReferenceSchoolEnrollment] ) 
    )
// Create ranked table based on table above
VAR RankedEnrollmentTbl = 
    ADDCOLUMNS(
        TempRankedEnrollmentTbl,
        "Rank",RANKX( TempRankedEnrollmentTbl, [TempRank],,ASC)
    )

[parry2k · Accepted solution]

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

[rbreneman]

@parry2k ,

This is awesome! Thank you so much for this!!

I have just one question. I was playing with the enrollment range slicer and noticed that it is breaking when there is a school with a tie (duplicate enrollment number). Any thoughts on how to overcome that? I'm guessing this is because RANKX is setting the rank as 1, 1, 3, etc... Is there a way I can get the RANKX to output as 1, 2, 3 in the event of a tie? The order doesn't really matter to me as long as both schools have a unique rank. I'm thinking alphabetically might make the most sense.

Thanks again! Really appreciate your help with this.

[parry2k]

@rbreneman is this what you are looking for?

[rbreneman]

@parry2k , yes, exactly!!

[parry2k]

@rbreneman maybe calculate the distance between reference

Rankx = 
VAR __enrollmentSchool = 1400
VAR __virtualTable = <<some table>>
VAR __rank = 
RANKX (
    ALL ( __virtualTable  ),
    ABS (  CALCULATE ( SELECTEDVALUE ( [Enrollment] ) ) - __enrollmentSchool ),, ASC )
RETURN __rank

✨ 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.

school enrolments with the virtual table stored in the variable and then calculate the rank

[rbreneman]

Hi @parry2k ,

I tried the solution you proposed however I get an error as I can't use a virtual table variable in the all function.

Here is a PBIX of the sample data that I'm playing with trying to get this to work if it would help you to visualize my goal: https://app.box.com/s/jpjijfic3b6k5mmhu2baaweup47rs439

My reference (selected school) enrollment is 1400 and I have a slicer that allows the user to select a percentage range that they want to see. If my range is set to -10% through 10% that means that I'm filtering schools with enrollment numbers of 1274 - 1526. I want to pull the top 3 similar schools to that enrollment number of 1400. In the sample dataset that would be 1398, 1397, and 1407 (tie between 2 districts - also need to figure out tie breaker).

I have all the logic inside of the Calculations measure. That is where I'm building the virtual table and adding a rank column using RANKX.

Please let me know of any other suggestions you may have on how to make this work. Thanks so much!

[parry2k]

@rbreneman what would be the output? You cannot return the table from a measure?

✨ 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.

[rbreneman]

Correct. The solution I'm looking for is simply a table stored as a variable inside my measure. I have other DAX written that will output specific values. (Measure is already at 262 rows...too long to share here and irrelevant). In short, I'll be outputting details on the schools that matches rank = 1, 2, and 3 using SWITCH.

Thanks!