How about a table calculation like:
IF FIRST()==0 THEN WINDOW_SUM(SUM([COST].[Cost]),0,IIF(FIRST()==0,LAST(),0)) END
for increased speed of calculation when you have lots of records.
and then set the pill's Compute using to "ID", as done in the attached.
jointest_edit.twbx 47.6 KB
Is there any reason why you can't define do the join in the connection, rather than using 2 connections and joining them with data blending?
If your two tables are in the same database (which includes 2 sheets in the same Excel spreadsheet, or 2 text files in the same directory), you could just define a multiple table connection, joined on ID and hey presto, no need for those nasty unintuitive table calcs.
Thanks, Joe. Your calc worked perfectly in the example file. Unfortunately, my real workbook is way more complex and contains way more data. I haven't been able to get the fix to work in that file. But I will keep trying.
And thanks also to you, Richard, for the suggestion. As luck would have it, I'm trying to join an Excel file to a SQL Server table. I went ahead and imported the Excel into SQL and created a view in the database. Works like a champ. But I still don't understand why I can't see the purpose for much less take advantage of the new Relationship functionality.
I agree, partitioning custom table calculations is not intuitive, but can be very powerful, and I believe can be a good solution. I would be more than happy to speak with you either via skype or any other collaboration tool you prefer, and help enable you to take advantage of table calculations in Tableau to allow you to get the results you are looking for. My email is joemako [at] gmail [dot] com.
If a table calc is not the best answer, I am more than happy to detail alternatives based on your constraints and specific situation.
My gut reaction after reading that you are trying to join an Excel file with a SQL Server table is, a data blend and a table calc is a good solution, and the the reason why the formula I provided above is because of partitioning, and I would be glad to share the lessons that I have learned, and how you can apply them to your situation.