Transforming a data range into separate rows using DAX and Fill down

Problem

There are instances where a data range needs to be transformed into rows.
e.g. Concert hall seat bookings. A customer may book a range of seats like from 1 to 5.

If you want to display the booked seats then you will need to have one row per seat with respective customer name who has booked the seat.

To solve this problem using Power Query check the blog Transforming a data range into separate rows using Power Query.

Source Data

Below is the “SeatBookings” table.

Capture

All Seats Table

You will need a table with all the seat numbers in sequence. Let’s call this the “SeatNumbers” table.

Capture

Solution in DAX

Create a table in DAX with the below:

DAX Table =
VAR JointTable =
    NATURALLEFTOUTERJOIN ( SeatNumbers, SeatBookings )
RETURN
    JointTable

This will produce the below table:

Capture

Now the challenge is to fill down the values of Customer, Seat Start and Seat End so that the blank cells can be filled.

LASTNONBLANK() DAX Function

The key function to achieve this is LASTNONBLANK() function.

It is important to know that “FirstNonBlank /LastNonBlank return the first/last value respectively in the column…..after sorting the column in its native Ascending Order….column, filtered by the current context, where the expression is not blank.”

The above is an extract from a blog by  titled Using FirstNonBlank / LastNonBlank in DAX. Zubair’s blog clearly explains how the LASTNONBLANK function works and how to use it correctly to get desired results.

Check the blog Filling missing values between dates to fill down values without using LASTNONBLANK() function.

Fill Down values using DAX

You need to create a new column using the below DAX to fill down the Customer values:

Customer Fill Down =
VAR LstNoBlankCustomer =
    CALCULATE (
        LASTNONBLANK ( ‘DAX Table'[SeatNum], 1 ),
        FILTER (
            ALL ( ‘DAX Table’ ),
‘DAX Table'[SeatNum] <= EARLIER ( ‘DAX Table'[SeatNum] )
&& NOT ( ISBLANK ( ‘DAX Table'[Customer] ) )
        )
    )
RETURN
    CALCULATE (
        MAX ( ‘DAX Table'[Customer] ),
        FILTER ( ALL ( ‘DAX Table’ ), ‘DAX Table'[SeatNum] = LstNoBlankCustomer )
    )

You need to create a new column using the below DAX to fill down the Seat Start values:

Start Seat Fill Down =
VAR LstNoBlankStart =
    CALCULATE (
        LASTNONBLANK ( ‘DAX Table'[SeatNum], 1 ),
        FILTER (
            ALL ( ‘DAX Table’ ),
‘DAX Table'[SeatNum] <= EARLIER ( ‘DAX Table'[SeatNum] )
&& NOT ( ISBLANK ( ‘DAX Table'[Seat Start] ) )
        )
    )
RETURN
    CALCULATE (
        MAX ( ‘DAX Table'[Seat Start] ),
        FILTER ( ALL ( ‘DAX Table’ ), ‘DAX Table'[SeatNum] = LstNoBlankStart )
    )

You need to create a new column using the below DAX to fill down the Seat End values:

End Seat Fill Down =
VAR LstNoBlankEnd =
    CALCULATE (
        LASTNONBLANK ( ‘DAX Table'[SeatNum], 1 ),
        FILTER (
            ALL ( ‘DAX Table’ ),
‘DAX Table'[SeatNum] <= EARLIER ( ‘DAX Table'[SeatNum] )
&& NOT ( ISBLANK ( ‘DAX Table'[Seat End] ) )
        )
    )
RETURN
    CALCULATE (
        MAX ( ‘DAX Table'[Seat End] ),
        FILTER ( ALL ( ‘DAX Table’ ), ‘DAX Table'[SeatNum] = LstNoBlankEnd )
    )

To identify the booked seats and empty (non-booked) seats you will need to create a new column as below:

Booked Customer =
IF (
    'DAX Table'[SeatNum] > 'DAX Table'[End Seat Fill Down],
    "Empty",
    'DAX Table'[Customer Fill Down]
)

The final table is below:

Capture

One drawback of this solution is that since new columns are created for large table this may not be very good solution due to additional memory usage due to the new columns.

A better approach is outline below.

Alternate Solution

A really elegant solution was presented by OwenAuger for a question I posted in Power BI forum.

NEW DAX Table =
VAR SeatNumbersPlusBookings =
    GENERATEALL (
SeatNumbers,
        GENERATE (
SeatBookings,
            INTERSECT (
                GENERATESERIES ( SeatBookings[Seat Start], SeatBookings[Seat End] ),
{ SeatNumbers[SeatNum] }
            )
        )
    )
VAR FinalTable =
    SELECTCOLUMNS (
        SeatNumbersPlusBookings,
        “SeatNum”, SeatNumbers[SeatNum],
        “Booked Customer”,
        VAR CurrentCustomer = SeatBookings[Customer]
        RETURN
            IF ( ISBLANK ( CurrentCustomer )“Empty”CurrentCustomer )
    )
RETURN
    FinalTable

Capture

In the DAX the value returned by each expression is dependent on the context it is evaluated in. In particular, GENERATEALL and GENERATE take the table supplied as the first argument, and evaluate the second argument (a table expression) in the row context of each row of the first argument.
image

Also the curly-braces syntax is a table constructor. In this case, {SeatNumbers[SeatNum]} creates a 1×1 table containing the SeatNum value from the current row of SeatNumbers. This is used to filter the series of seat numbers created by GENERATESERIES.

VAR “SeatNumbersPlusBookings’ can also be written as below using FILETR:

VAR SeatNumbersPlusBookings =
GENERATEALL (
SeatNumbers,
    GENERATE (
SeatBookings,
        FILTER (
            GENERATESERIES ( SeatBookings[Seat Start], SeatBookings[Seat End] ),
[Value] = SeatNumbers[SeatNum]
        )
    )
)

Visualisation

I used Card visuals and added the Booked Customer in the Fields. I added SeatNum into Visual Filter and set the advance filter as below.

Capture

Copy the Card visual and paste as many visual as needed depending on how many seats you have. Change the Visual Filter value to respective seat number. e.g. Card filter for seat number 2 will have the Visual filter value 2.

Capture

If you use a slicer to display bookings by Customer please note that all other Card visuals for other customers will display “Blank” as value. So if you need to see a customer booking along with empty seats then you will need to Ctrl click the Customer and ‘Empty’ in the slicer.

Capture

Advertisements

2 thoughts on “Transforming a data range into separate rows using DAX and Fill down

Add yours

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.

Up ↑

%d bloggers like this: