10 Replies Latest reply on Feb 28, 2013 9:45 AM by Sean Mullane

    measure expressions must be aggregate or depend only on group-by columns

    Richard Leeke

      I get this message from time to time when I'm defining the partitioning for Table Calculations.  If I just acknowledge the message and carry on everything usually seems to work OK, so I generally just ignore it.

       

      The workbook I'm working on at the moment has lots of calculated fields, often nested 3 or 4 deep, and I seem to get this message at least once for every nested calculation every time I try to change the partitioning - so it's becoming a bit of a pain.

       

      Does anyone know what it means or how to stop it?

       

      Looking in the logs I can't see any clues.  I just see bursts of these lines whenever I try to change partitioning:

       

       

      2011-07-09 02:10:01.288 (17f4): Error Dialog:

      2011-07-09 02:10:01.288 (17f4): An error occurred while communicating with the data source.

      2011-07-09 02:10:01.288 (17f4):

      2011-07-09 02:10:01.288 (17f4):    measure expressions must be aggregate or depend only on group-by columns

      2011-07-09 02:10:02.709 (17f4): RuntimeAssert: We have a Disconnect exception, but the data source name is empty!

      2011-07-09 02:10:33.569 (17f4): Error Dialog:

      2011-07-09 02:10:33.569 (17f4): An error occurred while communicating with the data source.

      2011-07-09 02:10:33.569 (17f4):

      2011-07-09 02:10:33.569 (17f4):    measure expressions must be aggregate or depend only on group-by columns

      2011-07-09 02:10:35.703 (17f4): RuntimeAssert: We have a Disconnect exception, but the data source name is empty!

      2011-07-09 02:10:35.730 (17f4): Error Dialog:

      2011-07-09 02:10:35.730 (17f4): An error occurred while communicating with the data source.

      2011-07-09 02:10:35.730 (17f4):

      2011-07-09 02:10:35.730 (17f4):    measure expressions must be aggregate or depend only on group-by columns

      2011-07-09 02:10:36.542 (17f4): RuntimeAssert: We have a Disconnect exception, but the data source name is empty!

        • 1. Re: measure expressions must be aggregate or depend only on group-by columns
          Richard Leeke

          Thanks Joe.  Interesting that you've never hit it (I presume there's a "not" missing from your first sentence) - I've hit it quite a few times and I'm sure you've done a lot more table calculations than me.  I wonder what I'm doing differently.

           

          I'll try to create a cut-down example.  I certainly haven't spotted any pattern to it and can't see anything wrong with what I'm doing.  I'm pretty sure the workbook is running fine and I'm getting all the right answers.

           

          I don't intentionally have a calculation used in different places with different partitioning.

           

          It doesn't give the error when I refresh the sheet - only when I go in to edit the partitioning - and often several times in succession before displaying the window to allow me to start editing.

          • 2. Re: measure expressions must be aggregate or depend only on group-by columns
            Joe Mako

            I do not remember experiencing any messages like that.

             

            Can you create a sample workbook, and steps to recreate the situation and messages?

             

            The first thing I can think of is if you are nesting calculated fields that use table calculations, if a calculated field is in use in more than once, it should have the same partitioning set for each occurrence. If you need to use the same logic in more than one place, but with different partitioning at the same time, you will want to duplicate the calculated field. I have found that Tableau allows me to use the same calc field in multiple nestlings at once, and allows me to set the partitioning differently, but it is not evaluating differently.

            • 3. Re: measure expressions must be aggregate or depend only on group-by columns
              Joe Mako

              > (I presume there's a "not" missing from your first sentence)

              Thanks, corrected above.

               

              I look forward to your example.

              • 4. Re: measure expressions must be aggregate or depend only on group-by columns
                Richard Leeke

                Simplest example I can come up with is attached.  Just try to edit the partitioning for the table calculation.

                 

                Removing the field from the filter shelf or either field from the rows shelf stops it happening.  Also changing the partitioning so it does not use Advanced stops it happening.

                 

                Obviously in this case I can get the same result without using Advanced - but in my real workbook I need to use the advanced option.

                 

                As I said, the workbook still gives the right answer - but the error is slightly disconcerting and mildly annoying - 4 extra clicks every time you edit  the partitioning.

                • 5. Re: measure expressions must be aggregate or depend only on group-by columns
                  Joe Mako

                  Here is my guess on what is happening:

                   

                  - Fields are either in the Dimensions or Measures listing section, and you can drag-n-drop numeric fields between them.

                  - When you place a field onto a shelf on a worksheet, it becomes a pill and you can set the pill to be either a dimension or a measure

                  - It is possible to have a field listed in the Measures section, and have the pill set to be a dimension.

                  - A field can be in the Measures section and converted to discrete, but being discrete is not the same a being dimension.

                  - Table calculations use pills that are set as dimensions for partitioning and addressing.

                  - When only using a single dimension pill for addressing (Compute using), the field can be listed in either Dimensions or Measures, but the pill must be a dimension in order for that field to be used for partitioning or addressing.

                  - When using more than one dimension pill for addressing, you get the error dialog when their fields are not listed in the Dimensions section.

                   

                  So this is only a factor when the field is listed in the Measures section and the pill is set as a dimension and when using more than one dimension pill for addressing (Compute using).

                   

                  What I am seeing is it will get an error but continue to work. This is not so for a Set that requires a field to be in the Dimensions section to be a part of a Set both to create the set, and you cannot move the numeric field back to the Measures section without breaking the Set.

                   

                  Try this: create a Set from the fields "B" and "C". In order to do so, both fields mush be a dimension. If you change the "C" field back into a measure, the Set will no longer work.

                   

                  Since the table calculation continues to work as expected after ok'ing the error dialog, the question I have is:

                  Should should this error dialog be removed, or should fields used for partitioning/addressing be required to be in the Dimensions section as well as be a dimension pill in order to be used in the Compute using settings (as is required for Sets)?

                   

                  Lately, I have gotten into the habit of creating a Set when I want to use multiple dimensions for addressing, and then setting the Compute using to that Set. Then I can set the sort order on the Set's pill instead of messing with the Advanced Compute using dialog. I have a few situations where this does not work in all situations, meaning sometimes I still have to use the Advanced Compute using dialog, The attached is how I would setup that worksheet, fields, pills, and compute using.

                   

                  This is an interesting situation, and may demand additional reflection, but I think this error dialog should be removed. Requiring a field to be listed in the Dimensions sections seems like an unnecessary restriction to me currently. I would expect that as long as a pill is set as a dimension, it can be used for partitioning/addressing, and if the pill is an aggregation that is set as a dimension, we continue to have the option to either ignore it or partition on it.

                   

                  I could also see a case made for adding a requirement that a field is listed in the Dimensions section to be able to be used in the Compute using settings. I have not yet found myself in a situation where I would need a field in the Measures section and used in the Compute using setting at the same time. So if the Compute using had the same recruitment as Sets, I think it would be okay. If I needed to use the field as a measure elsewhere, I could duplicate the field. The complexity comes into play when trying to explain this limitation, and handle for the user interface effects, I can see it becoming a hassle to require a field in a section to use it as wanted instead of just simply setting the pill as a dimension as the current capabilities are. I am also not sure why this would be required or why this is required for the creation of a Set. I expect there is some factor(s) that I am not aware of.

                  • 6. Re: measure expressions must be aggregate or depend only on group-by columns
                    Richard Leeke

                    Thanks Joe - I sort-of followed all that.  ;-)

                     

                    You can see why I didn't immediately deduce that that was what the error message was telling me.

                     

                     

                    OK - I have had a bit of a play with it, and whilst it's definitely very much connected to what you are saying there's more to it than that.  Try the following steps.

                     

                    1) Open the original example workbook I provided.

                    2) Select "Edit Table Calculation..." from the [Index] pill, to confirm the issue is happening.  Cancel the edit.

                    3) Convert field [C] to a dimension.

                    4) Select "Edit Table Calculation..." again and confirm that the issue no longer happens.  Cancel the edit.

                    5) Click the undo button on the toolbar to revert the workbook to it's starting state (undo button should now be dimmed to show that there are no unsaved changes).

                    6) Select "Edit Table Calculation..." again.  Note that the issue no longer happens, even though the workbook thinks it's state is unchanged from when it was opened.

                    7) Save the workbook.  This should not change state.

                    8) Select "Edit Table Calculation..." again.  Error message appears again - so save did change state in some way.

                     

                    Definitely something a bit wacky going on.  The newly saved version of the workbook is exactly the same as the original by the way (no differences with a file comparison utility).

                     

                    It still happens in the 6.1 beta, so I think I'll drop a note to support.  It really isn't a big deal because it doesn't actually prevent anything from working - it's just a bit confusing.

                    • 7. Re: measure expressions must be aggregate or depend only on group-by columns
                      Joe Mako

                      My first thought would be Tableau may be caching the query/result data, and it gets the error when it tries to evaluate with nothing cached, and the undo is not clearing the cache after the non-error evaluation. I agree, looks like a bug.

                      • 9. Re: measure expressions must be aggregate or depend only on group-by columns
                        Richard Leeke

                        I changed one numeric measure to a dimension and the error stopped happening on all of my calculated fields (and the result of the analysis was unchanged) - so thanks again for that bit of detective work, Joe.

                         

                        It's interesting - the distinction between measures and dimensions can often get a bit blurred, but I would definitely regard the role of this particular field as being a measure.  I'm calculating the average and total value of it per partition - but also the average and total of the top 8 values.  So I need the disaggregated values and I need to sort the partition by the individual values.  It still feels like a measure to me - but if Tableau wants me to call it a dimension before it allows me to do that without grumbling I'm happy with that.

                        • 10. Re: measure expressions must be aggregate or depend only on group-by columns
                          Sean Mullane

                          I just ran into a similar issue related to this. In case it helps anyone, here's a brief description:

                           

                          Using 7.0.12:

                           

                          I had a table calculation "count tasks" like

                          IF FIRST()==0

                          THEN

                               WINDOW_SUM(1)

                          END

                           

                          and another that referenced the first:

                          IF FIRST()==0

                          THEN

                               WINDOW_SUM([count tasks], 0, IIF(FIRST()==0, LAST(), 0))

                          END

                          which used a different partitioning.

                           

                          These worked fine when I was using a live connection to SQL Server (using custom SQL). When I turned this connection into an extract the viz would no longer load and it threw the error discussed above. The solution that worked for me was to replace the first table calc with a simple SUM(1). It didn't matter whether I replaced the formula for the first table calc or just dropped SUM(1) into the second table calc and scrapped the first one entirely. Note that SUM(1) worked, while trying just 1 still gave me the original error. As long as I was getting the error, the viz refused to load.

                           

                          I'm not sure why the error only came up when I used an extract, but there you go.