Forum Discussion
How to create custom slicer for multiple column
Hi Everyone,
I'm struggling to do the custom slicer. I hope you guys can help me. Here is the question, I have a lists of staffs with the lists of trainings courses and expiry status. What I want is that, in the power bi report I want to have:-
1. The slicer of training courses (Training A, Training B, Training C and etc. )
2. The slicer of expiry status (Not Required, Expired, <1 month, 1-2 months and >3 months)
3. Lists of name
So, when I click "Training A" and I click ">3 months", the lists of name will show the lists of staff who is in the Training A and the expiry is >3 months.
I tried to do the unpivot column (Link#1), but it will effect my other graph that shows the general status of training completion rate.
I also tried to do custom column (Link#2), but I can only do the Lists of Training Courses and I don't know how to link it with the Expiry Status.
Link#1: https://community.powerbi.com/t5/Desktop/Slicer-for-multiple-columns/m-p/337038#M150813
Link#2: https://community.powerbi.com/t5/Desktop/How-to-create-custom-column-for-a-slicer/td-p/551274
I hope you guys could help me.
Thank you.
Warm regards,
Atiyah
| Staff ID | Name | Training A | Training B | Training C | Training D | Training E | Training F |
| 1234567 | AAAA | Not Required | Not Required | Not Required | Not Required | Not Required | 1-2 months |
| 8901234 | BBBB | >3 months | >3 months | >3 months | >3 months | 1-2 months | <1 month |
| 5678901 | CCCC | <1 month | Expired | Not Required | Not Required | Expired | >3 months |
| 2345678 | DDD | >3 months | 1-2 months | 1-2 months | 1-2 months | Expired | <1 month |
| 9012345 | EEE | >3 months | 1-2 months | <1 month | >3 months | >3 months | >3 months |
| 6789012 | FFF | Expired | >3 months | 1-2 months | >3 months | <1 month | >3 months |
| 3456789 | GGG | 1-2 months | >3 months | Not Required | >3 months | Not Required | >3 months |
| 123456 | HHHH | >3 months | Expired | Expired | <1 month | >3 months | Expired |
| 7890123 | IIII | >3 months | <1 month | 1-2 months | <1 month | >3 months | >3 months |
| 4567890 | JJJJ | >3 months | >3 months | Not Required | >3 months | Expired | >3 months |
| 1234456 | KKKK | >3 months | Not Required | Not Required | >3 months | 1-2 months | Expired |
3 Replies
- Stachu
Community Champion
I think the easiest way is to go the Unpivot route and adjust the graph with the completion rates
how do you calculate the completion rates right now?
Hi Stachu,
Yes. In the interim period I'm doing it unpivot as well.
However, I have to upload 2 online excel into the desktop.
1. online excel for the % completion purpose.
2. another online excel for custom slicer purpose.
As for the % completion, I'm using the count completion to create the graph (last column from right) on order to create the graph as picture below.
- Stachu
if you calculate the completion rates in PowerBI rather than in Excel, then I think it would work fine
consider table like thisStaff ID Name Training Validity Status 1234567 AAAA Training A Not Required Not Required 1234567 AAAA Training B Not Required Not Required 1234567 AAAA Training C Not Required Not Required 1234567 AAAA Training D Not Required Not Required 1234567 AAAA Training E Not Required Not Required 1234567 AAAA Training F Valid 1-2 months 8901234 BBBB Training A Valid >3 months 8901234 BBBB Training B Valid >3 months 8901234 BBBB Training C Valid >3 months 8901234 BBBB Training D Valid >3 months 8901234 BBBB Training E Valid 1-2 months 8901234 BBBB Training F Valid <1 month 5678901 CCCC Training A Valid <1 month 5678901 CCCC Training B In Valid Expired 5678901 CCCC Training C Not Required Not Required 5678901 CCCC Training D Not Required Not Required 5678901 CCCC Training E In Valid Expired 5678901 CCCC Training F Valid >3 months 2345678 DDD Training A Valid >3 months 2345678 DDD Training B Valid 1-2 months 2345678 DDD Training C Valid 1-2 months 2345678 DDD Training D Valid 1-2 months 2345678 DDD Training E In Valid Expired 2345678 DDD Training F Valid <1 month and measures like this
Completion = CALCULATE(COUNTROWS('Table1'),'Table1'[Validity]="Valid") Total Training Courses = CALCULATE(COUNTROWS('Table1'),'Table1'[Validity]<>"Not Required") % Completion = DIVIDE([Completion],[Total Training Courses])they do give the same results as Excel in the employee filter context, you may just need to play a bit more with the filters to get the right totals (I'm not sure if 14/17 is consistent with your requirements for this four)
