Create list based on multiple columns

[Sander800]

Hi All,

I am new here so please correct me if I am wrong.

I am trying to create a cutsom filter list based on multiple columns. I have a table with 3 columns each column representing a hierachy level. I want to create a custom list (which I can then use for filtering) based on these three columns. The list does not need to contain all members of the columns but a manual selection. Consequently I would want to use this list to filter the table.

This is my table

Profit Center lvl1	Profit Center lvl2	Profit Center lvl3	Value
North	A	1	100
North	B	2	200
West	C	3	250
East	D	4	300
East	E	5	325
East	F	6	400

I would like to have a slicer to filter this table like below (different columns in 1 list). This list will be used for filtering the initial table.

List

North

A

B

5

6

[Anonymous · Accepted solution]

Naturally, that would happen, if you unpivot.

The reason, I'm asking, is that there is no intuitive way to slice the original table like you are asking for unless you create all the possible combinations of the 3 columns. That can be done in 2 ways (as I see it). Either unpivot or create all the combinations with a concatenate function (which, I think, is a very clumsy method). Either way, your database will grow in size. Unfortunately, I'm not familiar with any convenient way to slice like you are asking for.

[Anonymous]

Hi @Sander800

If I understand your challenge correctly, you'd like a hierachy slicer. There is a great custom visual for this purpose, which is called "Hierachy Slicer". You get it by looking for the "From Store" button in the menu under the headline "Custom Visuals". See screenshot below.

If this doesn't accommodate your problem, let me know, and I will try to help you solve the problem.

Best,

Martin

[Sander800]

Hi Martin,

Sorry for the late reply. I am aware of the hierarchy slicer, quite a nice visual (though too slow for large hierarchies).

I am actually not looking to see this in a hierachy. I am trying to get (manually) selected values from different columns in 1 flat list/ slicer.

[Anonymous]

Hi @Sander800

No problem. I see. So you want a column with all the values from your table, which you can then slice on? Is that correctly understood? If I don't understand correctly, could I ask you to elaborate a little more, possibly provide a new example? I really want to help you 🙂

Best,

Martin

[Sander800]

Hi Martin,

Appreciate the help!

I can manage a list based on 1 column :). I want to create a list based on multiple columns (see screenshot). I want to manually create a list of values from different columns.

For example in 1 flat list:

• North from colum Profit Center lvl1
• A and B from colum Profit Center lvl2
• 1, 2 and 3 B from colum Profit Center lvl3

[Anonymous]

Alright, I think I understand now. One quick question before I move on to a solution:

Is it necessary for "Profit Center lvl1", "Profit Center lvl2" and "Profit Center lvl3" to be seperate columns?

[Sander800]

I guess not per se, but if I would unpivot them it would increase my database size of course

[Anonymous · Accepted solution]

Naturally, that would happen, if you unpivot.

The reason, I'm asking, is that there is no intuitive way to slice the original table like you are asking for unless you create all the possible combinations of the 3 columns. That can be done in 2 ways (as I see it). Either unpivot or create all the combinations with a concatenate function (which, I think, is a very clumsy method). Either way, your database will grow in size. Unfortunately, I'm not familiar with any convenient way to slice like you are asking for.

[Sander800 · Accepted solution]

Hi Martin,

Thank you for all your help. Though not the outcome I hoped for at least now I know there are no alternate solutions.

KR,

Sander