4 Replies Latest reply on Aug 1, 2018 5:00 AM by Guy Wolf

    Creating measures using inter-related dimensions

    Guy Wolf

      I am struggling with the below problem and would appreciate some help. 


      What I am trying to achieve is a way of embedding some intelligence into the Tableau workbook about the inter-relational properties of various dimensions and measures.  I have attached a very simple example of this issue which illustrates it.


      If I have a data structure whereby a list of names (the primary key) are connected to other names in some way - specified as a dimension, how can I use this information within a calculated field.  In the example, I have three names, all of whom have four friends.



      I then have a second table which has a time series of values for every name



      I brought these together as an full outer join for Tableau which is all fine:


      However, what I then want to do is to be able to use the values within the dimensions as filters to select other values.


      For example, let's say I wanted to create a calculated field for the average wealth between a Name and Friend1, i.e. that would be the average of David and Tom if I filtered on David.  Or Fred and Francis if I filtered on Francis.


      If there is an answer, I suspect it is involving LOD expressions but I haven't managed to figure it out yet and my concern is that this is just something I need to do externally before bringing into Tableau so I want to check I am not wasting my time.  Obviously this is very simple example but the basic principle is fairly clear.  The workbook is attached.

        • 1. Re: Creating measures using inter-related dimensions
          Zhouyi Zhang



          I use inner join by 1=1 to your original data set as below

          And come up with something like this, if this is what you expected, please find attached workbook.


          Hope this helps



          • 2. Re: Creating measures using inter-related dimensions
            Ankit Bansal



            I didn't get you exactly. But i feel you need to pivot the data for primary source. You can use Tableau functionality of pivoting data(you can select all friends columns in data source pane and right click and say pivot). Your data will look like this:


            NAME , Pivot name , pivot value

            David,friend 1, Tom

            David,friend 2 , Dicg

            David,friend 3, Harry

            David,friend 4 , Bob


            then you can join this with your second source on name or Pivot value  or  may be 2 joins on both the columns with the same table twice separately as per your requirement.

            • 3. Re: Creating measures using inter-related dimensions
              Guy Wolf

              No, the question is not how to pivot data or not.  Unless, that is a requirement of the solution.  But whether this question is posed with the data being in rows or columns  - it is still the same issue.

              • 4. Re: Creating measures using inter-related dimensions
                Guy Wolf

                Thanks but that is not the question.  Maybe I was not clear enough.  The question is not whether some manual series of IF THEN statements can be written to address this example.  I just presented a very simple example to make it easy to understand.  But the target data set would be many thousands of key strings so any solution has to be entirely generated through functions.


                Essentially, the target is to be able to produce a calculated field outputting a measure such as this:


                Name:  GapToFriend3

                Function:  Wealth of <NAME> - Wealth of <FRIEND3>


                The result then would mean that you could filter by Name and get the difference between two Names but the Names which are in the calculation change according to the value in the dimension.


                It is important to maintain the specified hierarchy in the answer.  So you could have (for example) the ratio of FRIEND1 to FRIEND4 as a measure.


                The challenge is that you are selecting the 'Friend Names' on the basis of filtering the Dimensions for a Name.  But then the calculated measure is derived from filtering on the outputs from those Dimensions.


                This can be done very easily outside of Tableau but it is will create unnecessary amounts of extra data if it can be done within Tableau.