cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper I
Helper I

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
Helper I
Helper I

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
Helper I
Helper I

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

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.

 

Community Support
Community Support

Hi, @frikkiland 

 

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

Super User IV
Super User IV

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors