7 Replies Latest reply on Feb 18, 2016 10:26 AM by Bora Beran

    conver a calculated measure to dimension

    Sirish Bhatta

      Hello,

       

      I am trying to add a simple filter on a Tableau map. Basically what i am doing is create a calculated field as seen below, which Tableau considers as a measure and Tableau won't allow to convert it into a dimension.

       

      IF COUNTD(xyz_person) > 10 THEN 'Yes' ELSE 'NO' END    /* i need to use a Distinct here which is also being displayed in the map */

       

      Has anyone been able to convert this calculated field into a dimension so that you can add a Yes/No Filter.

       

      I am trying to avoid adding a extra custom SQL if that is the only route to go.

       

       

      Thanks.

       

      Tableau 9.0

       

      Thanks.

       

      Sirish

        • 1. Re: conver a calculated measure to dimension
          Daniel Vincent

          There must be something else with that field you're counting as you should be able to use that as a filter.  See ex. below using same calc, superstore data, and customer id in place of your dimension

           

          Screen Shot 2016-02-16 at 7.47.28 PM.png

          • 2. Re: conver a calculated measure to dimension
            lei.chen.0

            Hello Sirish,

             

            Is your xyz_person a field or a calculated field?

            When you have INCLUDE (LOD) in it, this may happen.

             

            For example, using superstore as data source,

             

            calculation 1: { INCLUDE [Product Name]:COUNT([Product ID])}

            calculation 2: IIF([Calculation1]>10,"yes","no")

             

            calculation 2 can not be converted to dimension.

             

            I don't know if this is your case, but my advice is to have a look at your xyz_person.

            If there is any INCLUDE (LOD) in it, you might have to rewrite calculation 2.

             

             

            Regards.

            • 3. Re: conver a calculated measure to dimension
              Bora Beran

              A field with an aggregate is considered a measure and cannot be converted to a dimension. This also applies to include and exclude LOD calcs. But you can convert a FIXED level of detail expression to a dimension. In fact this is a very common use case.

               

              If you would like to get the COUNTD(xyz_person) within each State

               

              instead of this

               

              IF COUNTD(xyz_person) > 10 THEN 'Yes' ELSE 'NO' END

               

              write

               

               

              IF {fixed State : COUNTD(xyz_person)}> 10 THEN 'Yes' ELSE 'NO' END

               

              and you will see that you are able to convert it to a dimension.

              1 of 1 people found this helpful
              • 4. Re: conver a calculated measure to dimension
                Sirish Bhatta

                Thanks Bora. I applied your solution and at first it seems to work. It created a dimension like you said and when i add a filter to the map, it seems to calculate correclty for the most part, when I toggle between Yes/No flag  but some are not correct, meaning When i say show me more than 10 , then for the most it only shows more than 10 , but for some, it is showing States  say like 7 , or 6 .

                 

                Even tried converting to Int  and checked o ensure that the  values are number and not string.  I do want to point out that what i am displaying is  STATE as a map, Sum_AMT and CNTD_xyz_person as  label inside each state in a map.

                 

                 

                 

                IF INT({ FIXED [State]: COUNTD([xyz_person]) })> 10 THEN 'Yes' ELSE 'No' END

                1 of 1 people found this helpful
                • 5. Re: conver a calculated measure to dimension
                  Bora Beran

                  Do those States have 7 in the table or 7 after a filter is applied. FIXED calculations happen before dimension filters.

                   

                  Since there is no workbook, this would be my first guess.

                   

                  If you could share the workbook, that is best way to understand the problem. If you can't at least sharing a screenshot of the sheet (so I can see what's on what shelf) would be a good first step.

                  • 6. Re: conver a calculated measure to dimension
                    Sirish Bhatta

                    Hi Bora, i got it to work after i removed some other filters and dimension that i had in place. 'Fixed' function concept is new to me ..now i just have to figure out how to handle when there are mutiple dimension filters with some exclusion applied as i believe i need to add the same dimensions to the the formula instead of just State dimension by adjusting the calculated field

                    IF INT({ FIXED [State]: COUNTD([xyz_person]) })> 10 THEN 'Yes' ELSE 'No' END

                     

                    Thanks a lot for your help.

                    • 7. Re: conver a calculated measure to dimension
                      Bora Beran

                      You could use include/exclude as well depending on what you have in your viz. E.g. if you have State, County but want to just get the numbers per state you can write it as

                       

                      {Exclude County : COUNTD([xyz_person])}

                       

                      and this will acknowledge the dimension filters.

                       

                      Or you can make dimension filters into context filters (click on the pill on the filter shelf and select 'add to context') then fixed will acknowledge them as well.