Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

ALLSELECTED from hierarchical slicer

Hello,

 

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

cat1level_2value  
cat1value   
cat2level_2level_3level_4value
cat3level_2level_3value 
cat4value   

 

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

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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:

catAcatA~subI~subII~subIII~subIV~val1    
catBcatB~subI~val2    
catCcatC~subI~sub2~sub3~sub4~sub5~val3    

 

2)

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

catAcatAsubIsubIIsubIIIsubIVval1 
catBcatBsubIval2    
catCcatCsubIsub2sub3sub4sub5val3

 

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):

 

catAcatAsubIsubIIsubIIIsubIV  
catBcatBsubI     
catCcatCsubIsub2sub3sub4sub5 

 

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

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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:

catAcatA~subI~subII~subIII~subIV~val1    
catBcatB~subI~val2    
catCcatC~subI~sub2~sub3~sub4~sub5~val3    

 

2)

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

catAcatAsubIsubIIsubIIIsubIVval1 
catBcatBsubIval2    
catCcatCsubIsub2sub3sub4sub5val3

 

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):

 

catAcatAsubIsubIIsubIIIsubIV  
catBcatBsubI     
catCcatCsubIsub2sub3sub4sub5 

 

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
Not applicable

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
Community Support
Community Support

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
Super User
Super User

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

 

Anonymous
Not applicable

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?

 

 

 

Yes. Also, share expected output

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.