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(SeatNumbers,SeatBookings)
VAR Test = 'JointTable'[SeatNum]*2
RETURN
JointTable

 

VAR Test is not working and the error message "Cannot find table 'JointTable'" is displayed.

 

 

Any help will be much appreciated.

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

9 Replies

  • OwenAuger's avatar
    OwenAuger
    Super User
    8 years ago

    Hi anandav

     

    It looks like there are two problems here:

     

    1. You are trying to assign an expression to the variable Test that includes a 'naked' reference to the SeatNum column, without being in a row context. Were you trying to add a column to JointTable? If so you would need to write something like
      VAR Test = ADDCOLUMNS ( JointTable, "SeatNum Doubled", SeatNumbers[SeatNum]*2 )
      Note I changed the column reference in red, see point 2 below.
    2. When you create a variable and assign a table value to it, like JointTable, you cannot follow the naming convention used with physical tables and subsequently refer to columns of the variable table as VariableName[ColumnName]. Instead:
      • If the column originated from a physical table without any renaming, which generally means linage is maintained, you can refer to it by its original fully qualified column name OriginalTable[OriginalColumn].
      • If the column was added with the name ColumnName (such as with ADDCOLUMNS, SELECTCOLUMNS or SUMMARIZE) then refer to it as [ColumnName]
      • In your example, I am guessing that SeatNum column comes from the SeatNumbers table. If so, within JoinTable it can be referred to as SeatNumbers[SeatNum].
      • Note that for a reference to a column of a table variable to even make sense, you must either be writing an expression in a row context (such as within ADDCOLUMNS, SUMX, FILTER), or providing a column reference to a function that acts on tables (such as SUMMARIZE).

     

    Could post back what final output you were looking for with New Table? It would help give you a precise answer on what you should do.

     

    Regards,

    Owen

    • anandav's avatar
      anandav
      Skilled Sharer
      8 years ago

      Hi OwenAuger,

       

      Thanks a lot for the detail reply. That is a very clear explanation.

       

      I was trying to create a table and fill down the missing values.

      Presently I have done it in two steps.

      1. Create the table.

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

       

      2. Then fill down the missing value in a new column.

      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 )
          )

       

      Repeat the new column step for Seat Start and Seat End.

       

      So the final table is :

       

      What I was trying to achieve is instead of creating the virtual table and then adding columns to it do it in a single dax create table step.

      • 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