5 Replies Latest reply on Feb 20, 2019 3:33 PM by Kaz Shakir

    Most dimension for all (as a MEASURE)

    Diogo Braga

      The data set has a group of schools serving different grades.

      The goal is to show PK to 12 grades for every single school as MEASURE as in the example provided, but color-code only the grades that the school serves. How can I do that?



      This is what I have now...


      But it needs to look something like this...


        • 1. Re: Most dimension for all (as a MEASURE)
          Kaz Shakir


          I'm not sure if this approach will work for you, but could you do the following:

          First make a new datasource that just contains the "Grades."  It might look something like this:

          Next, change your original data source to use the "Grade_Id" field (the reason will become clear shortly):

          Then in your workbook, set up the "relationship" between the two data sources, as follows:


          Then place the "School" field on the Rows shelf, and also add the "Number Of Records" measure to the row shelf, so that your Row shelf now looks like this:

          Next, create a placeholder calculated field that just contains a space, like so:


          Then. place the "Grade Id", "Grade Name", and "placeholder" fields from the other data source, on the columns shelf, like so (notice the orange check mark that indicates these fields are from the secondary datasource):

          Then, place the "Grade Name" field on the "Label" tile, on the Marks shelf:

          Next, right-click on the "axis" that shows "Number of Records" and select "Edit Axis" from the menu.  Change the Rage selection from "Automatic" to "Fixed", and in the Fixed Start and Fixed End area, make sure it shows "0" and "1", like so:


          Then, click the "Size" tile on the Marks shelf, and set it to the maximum size.

          Finally, right-click on each of the "Grade Id", "placeholder", and "Sum(Number Of Records)" fields, and un-check the "Show Header" option.  Then you should have a vizualization that looks something like this:


          Hope that helps.


          • 2. Re: Most dimension for all (as a MEASURE)
            Diego Parker

            Hey Diogo,


            If you don't want to create a secondary data source you can use a table calculation to achieve the same result.

            Example Diogo.PNG


            Please find my solution attached.


            I created a table calculation relative to the first school which has all grades.


            Example Diogo2.PNG


            I hope this helps. If you found this helpful or answered your question, please mark it as helpful/correct so other users can refer to it.


            If you have any questions do not hesitate on asking.




            2 of 2 people found this helpful
            • 3. Re: Most dimension for all (as a MEASURE)
              Diogo Braga

              Thank you, but I am looking for a simpler solution that doesn't involve data blending.

              The data connections in the real workbook are already pretty involved.

              • 4. Re: Most dimension for all (as a MEASURE)
                Diogo Braga

                Thanks, but for my purposes grades need to be a measure in columns.

                • 5. Re: Most dimension for all (as a MEASURE)
                  Kaz Shakir


                  How about if you try the following:

                  1.  Clear the table calculation on the "CNT(Grade)" measure that you have on the Columns shelf:


                  2. Next, place the "Grade" Dimension on the Columns shelf, in addition to the Count Measure you already have there:


                  3. Then, right click the axis at the bottom, and select "Edit Axis", and change it to "Fixed" and change the "Fixed end" to 1:


                  4. Then, click the "Label" tile on the Marks shelf, and select Alignment, and make sure you are center aligned:


                  5. Right-click the blue "Grade" tile on the columns shelf, and select sort, and then mark "Manual" under "Sort by", and rearrange the values so that PK and K appear at the top of the list:


                  6. Finally, right click the green "CNT(Grade)" pill on the Columns shelf, and un-check the "Show Header" item.  Your, final visualization, should then look like this:


                  Please let us know if that gets to the solution you need.