1 Reply Latest reply on Aug 22, 2016 4:00 AM by Ben Neville

    Repeat Row Values per Partition

    sina.d

      Hi All,

       

      I am simply trying to repeat the values of a (calculated) dimension for each partition.

       

      I dont know it I am making sense so I have attached from prototyped which I have put together in excel, which illustrates how I want it to be displayed.

       

      The Comment field is calculated like this:

      IF ISNULL([Category])then [Answer] END

       

       

      Any advice would be much appreciated.

       

      Thanks.

        • 1. Re: Repeat Row Values per Partition
          Ben Neville

          Hi Sino - 2 options here.

           

          First it might be important for you to understand why Tableau is returning these results. Your calculation is a disaggregated calculation, which means it is computed for every row of your data. For the rows where the Category IS null, this calculation works. For rows where the Category IS NOT null, the first condition (ISNULL([Category])) is false. This would normally skip your true result ([Answer]) and return your false condition answer (normally what is in the "ELSE..." part of the calculation), but because there is no result provided for a false condition, the calculation returns NULL. In addition, you don't actually want to output the comment that is on the same row in the Answer column - rather, you want to lookup the Null category comment.

           

          The above criteria ALWAYS means a table calculation or a level of detail expression. This is because in your raw data, what you want to compute against or return is in a different row. To understand these topics better, you should watch some videos on Table Calculations and LoD Expressions. Once you have done this, you can pick which approach is right for you. Assuming you have a table laid out like what is in your screenshot, you can:
          1. Use a table calculation which calculates this for the NULL row, and looks up the result if it's not a NULL row. You can make this logic more robust, but in your example, as a NULL is always first, here we just look up the first row in the table:
          LOOKUP(MIN(IF ISNULL([Category]) THEN [Answer] END),FIRST())
          You can then partition this as per the below screenshot (more videos available on this if you want to learn more):
          Table Calc Partition.jpg

          2. You could also use an LoD expression and not have to worry about partitioning and table calculations. It's a bit easier, but also has filter and other implications, which you should learn if you watch the LoD tutorial videos. For this, the calculation is simpler and should "just work":
          {FIXED [ID]: MIN(IF ISNULL([Category]) THEN [Answer] END)}

          1 of 1 people found this helpful