Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
Hi @anandav
It looks like there are two problems here:
VAR Test = ADDCOLUMNS ( JointTable, "SeatNum Doubled", SeatNumbers[SeatNum]*2 )Note I changed the column reference in red, see point 2 below.
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
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.
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
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.
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
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.
Thanks for the approval @OwenAuger.
Yes, I am in Auckland and have been to to your presentation in the Auckland Power BI forum.
Hopefully we can catch up when you are there next time.
Hi @OwenAuger,
Really elegant solution. My solution will not be memory efficient for large tables. Your solution is really good.
Thanks a lot for taking time to help solve this.
PS. I realised I have a lot more to learn/understand on using DAX.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |