Forum Discussion

anandav's avatar
anandav
Skilled Sharer
8 years ago
Solved

How to reference columns in virtual tables?

Hi,   I am creating a virtual table usingVAR. How can I refer to the columns of this newly created virtual table in the same table creation DAX? New Table = VAR JointTable = NATURALLEFTOUTERJOIN(...
Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    Hi again anandav

     

    Thanks for the additional info.

    From what you've provided, could I suggest a different approach, as doing the fill-down in DAX is possible but a little awkward.

     

    I'm making an assumption that you are really interested in SeatNum and Booked Customer from your screenshot below. If so, I would propose this:

     

     

    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

    I'm using GENERATEALL to do the join rather than NATURALLEFTOUTERJOIN since your physical SeatBookings table don't have all the required values (since it contains Seat Start and Seat End), and GENERATE to convert the start/end values to a range.

     

     

    You could of course include additional columns on top of the two output by the above.

     

    Regards,

    Owen

OwenAuger's avatar
OwenAuger
Super User
8 years ago

Hi again anandav

 

Thanks for the additional info.

From what you've provided, could I suggest a different approach, as doing the fill-down in DAX is possible but a little awkward.

 

I'm making an assumption that you are really interested in SeatNum and Booked Customer from your screenshot below. If so, I would propose this:

 

 

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

I'm using GENERATEALL to do the join rather than NATURALLEFTOUTERJOIN since your physical SeatBookings table don't have all the required values (since it contains Seat Start and Seat End), and GENERATE to convert the start/end values to a range.

 

 

You could of course include additional columns on top of the two output by the above.

 

Regards,

Owen

anandav's avatar
anandav
Skilled Sharer
8 years ago

Hi OwenAuger,

 

I was trying to understand the solution but ran into some problems. I tried to seperate each part of the DAX into VARs but it gives different results compared to using all in one DAX statement.

 

Table =
 VAR SeatsINBookedRange = GENERATESERIES ( MIN(SeatBookings[Seat Start]), MAX(SeatBookings[Seat End]) )
 VAR AllSeats = SeatNumbers
 VAR BookedAndEmptySeats = INTERSECT(SeatsINBookedRange, AllSeats)
 VAR Test1 = GENERATE(SeatBookings, BookedAndEmptySeats)
 VAR CustomerSeatBookings =
        GENERATE (
            SeatBookings,
            INTERSECT (
                GENERATESERIES ( SeatBookings[Seat Start], SeatBookings[Seat End] ),
                SeatNumbers
            )
        )
//VAR Test2 = GENERATEALL(SeatBookings, CustomerSeatBookings)
VAR SeatNumbersPlusBookings =
    GENERATEALL (
        SeatNumbers,
        GENERATE (
            SeatBookings,
            INTERSECT (
                GENERATESERIES ( SeatBookings[Seat Start], SeatBookings[Seat End] ),
                { SeatNumbers[SeatNum] }
            )
        )
    )
RETURN
SeatNumbersPlusBookings
 
Questions:
1.  VAR SeatsINBookedRange = GENERATESERIES ( MIN(SeatBookings[Seat Start]), MAX(SeatBookings[Seat End]) )
Thoug in the compsite DAX statement SeatBookings[Seat Start] can be referenced when in the VAR I had to use MIN and MAX functions).
Why is thsi?
 
2.  VAR AllSeats = SeatNumbers
Couldn't create a table with {} as it is not allowed.
"A single value for column 'SeatNum' in table 'SeatNumbers' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
 
3.  VAR BookedAndEmptySeats = INTERSECT(SeatsINBookedRange, AllSeats)
Returns the same table as in Step#1. Value from 1 to 19
 
4.   VAR Test1 = GENERATE(SeatBookings, BookedAndEmptySeats)
Return wrong results which is a cartisian product of tables.
 
 
5.  VAR CustomerSeatBookings
Combination of steps 1,2,3,4 in single DAX statement gives correct esults.
 
6. VAR Test2 = GENERATEALL(SeatBookings, CustomerSeatBookings) 
Gives an error "Function GENERATEALL does not allow two columns with the same name 'SeatBookings'[Customer]."
 
7.  VAR SeatNumbersPlusBookings
From your solution, gives the correct results.

If I change  { SeatNumbers[SeatNum] } to SeatNumbers table reference the results are wrong. What is  { SeatNumbers[SeatNum] } means?
Will you be able to help explain why the compsite DAX statement works and the individual statements don't?
Thanks in advance. 
  • OwenAuger's avatar
    OwenAuger
    Super User
    8 years ago

    Hi again anandav

     

    Thanks for the follow-up questions!

     

    I may have to give you a more detailed answer later, but the general explanation is that 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.

     

    When you evaluate the various expressions independently, you get strange results because they were intended to be evaluated within a particular (row) context. Also, in a row context, you can refer to the values of columns in the current row with a "naked" column reference, such as SeatBookings[Seat Start].

     

    Diagram below:

     

     

    Also the curly-braces syntax is a table constructor. In this case, { SeatNumbers[SeatNum] } creates a 1x1 table containing the SeatNum value from the current row of SeatNumbers. I am using this to filter the series of seat numbers created by GENERATESERIES. Looking at this again, I could just as well have used FILTER, as in something like FILTER ( GENERATESERIES(...), [Value] = SeatNumbers[SeatNum] )

     

    Let me know if that helps - I will try to get back and reply on your specific questions later though.

     

    Best regards,

    Owen

    • anandav's avatar
      anandav
      Skilled Sharer
      8 years ago

      Thanks a lot for the detail explanation Owen.

      DAX gets confusing at times since some functions like clauclate we have to work from outer function to inner fucntion and others from inner to oueter (as I understand). But your diagram helps a lot!

       

      Is it ok if I use your explanation in the blog I have done with credit to you?

      I have added the solution with credit to you but also wanted to include this explanation iof it is ok with you.

      • OwenAuger's avatar
        OwenAuger
        Super User
        8 years ago
        Sure, no problem, feel free to use it :)
        Glad it helped.
        Oh I see you are also in Auckland, so may well run into you some time 😀