Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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.
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
isblank([column]) is a function check blank values
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |