8 Replies Latest reply on May 24, 2018 9:37 AM by Keiljuana Phillips

# Create a Dimension of Unique Combination of Values from 2 Dimensions

I am trying to create a concatenated value of two values that have been combined to have every possible combination/permutation. However, in having the data organized this way i dont end up with unique values because I get 'Value1-Value2' and 'Value2-Value1' which actually represent the same paired combination.

What i'd like to do is have these values roll up into one. I've been trying to do a table calculation to lookup the previous value and if they are the same then exclude the second one but it hasn't cleanly been working.

I created a sample workbook with just dimensions but can make a more robust one if necessary. I dont care which combination of the above comes up but I just want one.

In the sample workbook I used fruit as the example.

Pair 1 - Apple-Orange

Pair 2 - Orange-Apple

they effectively mean the same thing.

• ###### 1. Re: Create a Dimension of Unique Combination of Values from 2 Dimensions

Use MIN and MAX:

MIN([Fruit 1],[Fruit 2])+'-'+MAX([Fruit 1],[Fruit 2])

With this

Pair 1 - Apple-Orange

Pair 2 - Orange-Apple

will both be Apple-Orange

• ###### 2. Re: Create a Dimension of Unique Combination of Values from 2 Dimensions

This worked well for me, elegant solution. I didn't think to use Min/Max functions.

Thank you.

• ###### 3. Re: Create a Dimension of Unique Combination of Values from 2 Dimensions

You are welcome. Though there's more than one way to skin a cat, some ways are easier than the other. I prefer the simplest solution.

• ###### 4. Re: Create a Dimension of Unique Combination of Values from 2 Dimensions

Thanks Prayson for that answer. I have a question very similar to Chris'. I have the following accounts with various combinations. I ultimately want to get to the results displayed in the very last column (labeled Program Combinations), but within Tableau. I tried using the same min/max statement, but that didn't work. Do you have another suggestion for how I might be able to achieve the desired results? I have pasted a screenshot below of a sample data set.

• ###### 5. Re: Create a Dimension of Unique Combination of Values from 2 Dimensions

Hej Keiljuana, Sorry for a late reply. Could you how your dummy data sample look-like and what expected result you will like to have at the end? You can do it on Excel

• ###### 6. Re: Create a Dimension of Unique Combination of Values from 2 Dimensions

Create a Dimension of Unique Combinations of Values from One Dimension

Hello Prayson,

I have created a forum to discuss the above mentioned question. Feel free to review the sample workbook that I've included within the posting. I am having difficulties attaching a document within my reply to you, so I've included a screenshot of the data. Let me know if that helps.

• Entire Customer List - This is a mocked list of the customer set. Notice how "Business Partner No." can be enrolled in various Program Types.

• Unique Combinations (No Dups) - I have taken all of the variations (column labeled Program Combinations from the first spreadsheet) and pasted within the spreadsheet

• Percentages of Unique Combinations - The outcome desired. The percentages are listed based on the number of occurrences from the first sample spreadsheet.

Thanks for responding.

Keiljuana

• ###### 7. Re: Create a Dimension of Unique Combination of Values from 2 Dimensions

Something similar is answered here. Re: Count concatenated columns from multiple rows.

Let me know if this not the same

• ###### 8. Re: Create a Dimension of Unique Combination of Values from 2 Dimensions

Prayson,

Thanks for responding. For some reason the outcome produced within the link that you provided is not working for my data set. I don't want to say that it's not the same, but again, I'm not getting the desired output.