Finding the closet group of friends in classroom

DAX DAX Calculations
8

@Tanzeel,

Here’s the DAX-based solution I worked up in response to your requirement. The first thing I did was add a date table and marked it as date table, to ensure any time intelligence functions worked correctly.

I then added a series of simple calculated columns in the Class Room table (similar to Melissa’s Power Query approach), adding columns for left and right offsets, and conditions to check for end of row left and right, as well as max chairs per row.

image
image.png1111×346 21 KB

Next, I created two measures to calculate the left and right neighbors. They are very similar in structure, just differing in the direction of the shift and the boundary condition tests. Each one takes the selected dates, rows, chairs and students from the slicers, iterates through the relevant chairs, shifting them left or right, testing the boundary conditions to make sure the row end isn’t overrun, and then concatenates the R#C# text ID for the occupied seats to represent the left and right neighbors.

Here’s the Left Neighbor measure:

Chair ID to Left = 

VAR Occupied =
SELECTEDVALUE( 'Class Attendance'[Status] )

VAR LeftShift =
SELECTEDVALUE( 'Class Attendance'[Asset Chair] ) - 1

VAR IDCalc =
CALCULATE(
    CONCATENATE(
        CONCATENATE("R",SELECTEDVALUE( 'Class Attendance'[Asset Row] )),
        CONCATENATE("C", LeftShift)
    ),
    ALLSELECTED( 'Class Attendance'[Date], 'Class Attendance'[Asset] )
)

RETURN
SWITCH( TRUE(),
    Occupied = 0, BLANK(),
    LeftShift = 0 || LEFT( IDCalc, 2) = "RC", BLANK(),
    IDCalc
)

And the Right Neighbor measure:

Chair ID to Right = 

VAR Occupied =
SELECTEDVALUE( 'Class Attendance'[Status] )

VAR RightShift =
SELECTEDVALUE( 'Class Attendance'[Asset Chair] ) + 1

VAR RightMax =
LOOKUPVALUE(
    'Class Room'[Max Chair by Row],
    'Class Room'[ID],
    SELECTEDVALUE( 'Class Attendance'[Asset] )
)

VAR IDCalc =
CALCULATE(
    CONCATENATE(
        CONCATENATE("R",SELECTEDVALUE( 'Class Attendance'[Asset Row] )),
        CONCATENATE("C", RightShift)
    ),
    ALLSELECTED( 'Class Attendance'[Date], 'Class Attendance'[Asset] )
)

RETURN
SWITCH( TRUE(),
    Occupied = 0, BLANK(),
    RightShift > RightMax || LEFT( IDCalc, 2) = "RC", BLANK(),
    IDCalc
)

And here it is all put together:

image
image.png1357×684 78.7 KB

Thanks – fun problem to work on. Hope this is helpful. Full solution file posted below.

1 Like