ALLSELECTED from hierarchical slicer

[Anonymous]

Hello,

I have 10 columns on my table and I create a slicer in hierarchical format, like as the example below:

cat1	level_2	value
cat1	value
cat2	level_2	level_3	level_4	value
cat3	level_2	level_3	value
cat4	value

I have a hierarchical slicer where catX is the main category, and of course, I need to choose multiples values. I tried to achieve it when all levels (parent-child have same number of elements), but I have asimetric matrix (each level_1 can contain different sub-levels).

The problem is, I solved in part to show in the table (and on the cart like in legends), each value choosen before on the slicer, and, in first instance I create new column like this:

lastnoblankValue = SWITCH(TRUE();
            categories[tag_A.10]<>BLANK()categories[tag_A.10];
            categories[tag_A.9]<>BLANK();(categories[tag_A.9];
            categories[tag_A.8]<>BLANK();(categories[tag_A.8];
            categories[tag_A.7]<>BLANK();categories[tag_A.7];
            categories[tag_A.6]<>BLANK();categories[tag_A.6];
            categories[tag_A.5]<>BLANK();categories[tag_A.5];
            categories[tag_A.4]<>BLANK();(categories[tag_A.4];
            categories[tag_A.3]<>BLANK();(categories[tag_A.3];
            categories[tag_A.2]<>BLANK();categories[tag_A.2];
            BLANK()

I achieve to show last not blank value. So, when I choose an element from the slicer I have a reference. 

But I get only each value in each level, not all in case I selected multiple values per each category.

If I choose

Is it possible? How I to be able to achieve it?

thank you

[Anonymous · Accepted solution]

Hello,

I explain again the follow problem, so I rewrite this post because I figured out the problem.

Last noblank columns is value and I need to extract it to reindex and create the correct unpivoted column or create a slicer with less levels on hierarchy menu. (I need to calculate after conversion, and put the values on the chart, matrix and legend).

1) 

I have originally this table:

catA	catA~subI~subII~subIII~subIV~val1
catB	catB~subI~val2
catC	catC~subI~sub2~sub3~sub4~sub5~val3

2)

After split columns I have category and sublevels (parent and child) like this format:

catA	catA	subI	subII	subIII	subIV	val1
catB	catB	subI	val2
catC	catC	subI	sub2	sub3	sub4	sub5	val3

3)

If I extract lastnoblank value, I take only VALUES and these ones reduce hierarchical levels on my slicers. So finally with the calculated columns I have:

lastnoblankValue = SWITCH(TRUE();
            categories[tag_A.10]<>BLANK()categories[tag_A.10];
            categories[tag_A.9]<>BLANK();(categories[tag_A.9];
            categories[tag_A.8]<>BLANK();(categories[tag_A.8];
            categories[tag_A.7]<>BLANK();categories[tag_A.7];
            categories[tag_A.6]<>BLANK();categories[tag_A.6];
            categories[tag_A.5]<>BLANK();categories[tag_A.5];
            categories[tag_A.4]<>BLANK();(categories[tag_A.4];
            categories[tag_A.3]<>BLANK();(categories[tag_A.3];
            categories[tag_A.2]<>BLANK();categories[tag_A.2];
            BLANK()

This colum is valid, because I have lastnoblank value from each row.

Now, Could I to remove these values from columns? I saw power query M functions that transform columns or list , and create a new columns like (without values):

catA	catA	subI	subII	subIII	subIV
catB	catB	subI
catC	catC	subI	sub2	sub3	sub4	sub5

Is it possible to achieve it?

Final result will be:

On slicer appears:

catA > val1 

catB > val2

catC > val3

If is select first one:

On Legend appears:

subI > subII > subIII > subIV > val1

On the Matrix table appears:

val1

[Anonymous]

In attachment my final result (has been modified to achieve expected result).

https://1drv.ms/u/s!AofhffESINwYh0Q9IoYbk2avRCCb?e=TxRrxK

Manually I re-create a new excel with only extracted values from splitted columns, I leave blank columns (originally was merged). Now, when I create slicer, matrix and Legends I can show my expected results.

Important : Original table from database, merged columns are nested categories, sublevels and values, for this reason I need to extract lastnoblank value, remove from columns and manipulate hierarchy slicer.

[v-alq-msft]

Hi, @Anonymous 

Could you please show us some sample data and expected results with One Drive for business? Do mask sensitive data before uploading.

Best Regards

Allan

[amitchandak]

isblank([column]) is a function check blank values

[Anonymous]

Hi @amitchandak  thank you, but it don't solve my issue.

I put code above in new column and now it get last value per each row (multiple columns reduced in one only). Fine works, but when I select this columns into matrix or chart on legends, when I select one or more elements into slicer, only it shows elements per each levels, if I select one per category A , and one on category C (and share same levels), the formula only get one per levels...

Did you need more info or screenshot?

[amitchandak]

Yes. Also, share expected output