Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Dear community,
I have a problem with creating a custom slicer containing values with overlapping data.
My fact table contains a lot of financial data and each row in the table is marked with a certain account number (TILIRYHMA).
I had a slicer based on the TILIRYHMA field, but the customer feels it is tiresome to select the account numbers one by one from the slicer to filter data the way they want.
Instead, they’d like to group the account (TILIRYHMA) numbers so that if customer selects from the slicer one group, it would return rows containing only selected account numbers.
I tried using SWITCH function to group the account (TILIRYHMÄ) numbers into groups, and use the result as a slicer, but looks like the SWITCH function is “exclusive” what comes to evaluating the switch conditions one after another.
I.e. the problem is that, for example, one group should contain account numbers from {300, 310, 320, 330, 340, 350} and another group should contain values < 700 -> so the latter group condition should have values from the first condition, too. But they are excluded from the latter group because the already belong to the first group in the SWITCH (TRUE) function.
I even tried to create separate columns based on the account numbers (whether the number belongs to the group or not = T/F) but that did not help.
Here's the table I used to test the grouping and filtering (this is not the actual fact table just a test table)
TILIRYHMA | Tulos T/F | Liikevoitto T/F | Liikevaihto T/F | Kiinteat kulut T/F | Muuttuvat kulut T/F |
104 | TRUE | TRUE | FALSE | FALSE | FALSE |
106 | TRUE | TRUE | FALSE | FALSE | FALSE |
110 | TRUE | TRUE | FALSE | FALSE | FALSE |
121 | TRUE | TRUE | FALSE | FALSE | FALSE |
122 | TRUE | TRUE | FALSE | FALSE | FALSE |
123 | TRUE | TRUE | FALSE | FALSE | FALSE |
124 | TRUE | TRUE | FALSE | FALSE | FALSE |
125 | TRUE | TRUE | FALSE | FALSE | FALSE |
130 | TRUE | TRUE | FALSE | FALSE | FALSE |
140 | TRUE | TRUE | FALSE | FALSE | FALSE |
141 | TRUE | TRUE | FALSE | FALSE | FALSE |
142 | TRUE | TRUE | FALSE | FALSE | FALSE |
144 | TRUE | TRUE | FALSE | FALSE | FALSE |
145 | TRUE | TRUE | FALSE | FALSE | FALSE |
150 | TRUE | TRUE | FALSE | FALSE | FALSE |
152 | TRUE | TRUE | FALSE | FALSE | FALSE |
153 | TRUE | TRUE | FALSE | FALSE | FALSE |
154 | TRUE | TRUE | FALSE | FALSE | FALSE |
155 | TRUE | TRUE | FALSE | FALSE | FALSE |
160 | TRUE | TRUE | FALSE | FALSE | FALSE |
161 | TRUE | TRUE | FALSE | FALSE | FALSE |
162 | TRUE | TRUE | FALSE | FALSE | FALSE |
163 | TRUE | TRUE | FALSE | FALSE | FALSE |
166 | TRUE | TRUE | FALSE | FALSE | FALSE |
180 | TRUE | TRUE | FALSE | FALSE | FALSE |
190 | TRUE | TRUE | FALSE | FALSE | FALSE |
200 | TRUE | TRUE | FALSE | FALSE | FALSE |
201 | TRUE | TRUE | FALSE | FALSE | FALSE |
203 | TRUE | TRUE | FALSE | FALSE | FALSE |
205 | TRUE | TRUE | FALSE | FALSE | FALSE |
220 | TRUE | TRUE | FALSE | FALSE | FALSE |
232 | TRUE | TRUE | FALSE | FALSE | FALSE |
250 | TRUE | TRUE | FALSE | FALSE | FALSE |
253 | TRUE | TRUE | FALSE | FALSE | FALSE |
258 | TRUE | TRUE | FALSE | FALSE | FALSE |
260 | TRUE | TRUE | FALSE | FALSE | FALSE |
273 | TRUE | TRUE | FALSE | FALSE | FALSE |
275 | TRUE | TRUE | FALSE | FALSE | FALSE |
276 | TRUE | TRUE | FALSE | FALSE | FALSE |
278 | TRUE | TRUE | FALSE | FALSE | FALSE |
279 | TRUE | TRUE | FALSE | FALSE | FALSE |
280 | TRUE | TRUE | FALSE | FALSE | FALSE |
282 | TRUE | TRUE | FALSE | FALSE | FALSE |
300 | TRUE | TRUE | TRUE | FALSE | FALSE |
310 | TRUE | TRUE | TRUE | FALSE | FALSE |
320 | TRUE | TRUE | TRUE | FALSE | FALSE |
330 | TRUE | TRUE | TRUE | FALSE | FALSE |
340 | TRUE | TRUE | TRUE | FALSE | FALSE |
350 | TRUE | TRUE | TRUE | FALSE | FALSE |
360 | TRUE | TRUE | FALSE | FALSE | FALSE |
390 | TRUE | TRUE | FALSE | FALSE | FALSE |
400 | TRUE | TRUE | FALSE | FALSE | TRUE |
411 | TRUE | TRUE | FALSE | FALSE | TRUE |
415 | TRUE | TRUE | FALSE | FALSE | TRUE |
420 | TRUE | TRUE | FALSE | FALSE | TRUE |
421 | TRUE | TRUE | FALSE | FALSE | TRUE |
422 | TRUE | TRUE | FALSE | FALSE | TRUE |
430 | TRUE | TRUE | FALSE | FALSE | TRUE |
450 | TRUE | TRUE | FALSE | TRUE | FALSE |
460 | TRUE | TRUE | FALSE | TRUE | FALSE |
462 | TRUE | TRUE | FALSE | TRUE | FALSE |
470 | TRUE | TRUE | FALSE | TRUE | FALSE |
500 | TRUE | TRUE | FALSE | TRUE | FALSE |
510 | TRUE | TRUE | FALSE | TRUE | FALSE |
520 | TRUE | TRUE | FALSE | TRUE | FALSE |
530 | TRUE | TRUE | FALSE | TRUE | FALSE |
540 | TRUE | TRUE | FALSE | TRUE | FALSE |
550 | TRUE | TRUE | FALSE | TRUE | FALSE |
600 | TRUE | TRUE | FALSE | FALSE | FALSE |
610 | TRUE | TRUE | FALSE | TRUE | FALSE |
611 | TRUE | TRUE | FALSE | TRUE | FALSE |
620 | TRUE | TRUE | FALSE | TRUE | FALSE |
630 | TRUE | TRUE | FALSE | TRUE | FALSE |
640 | TRUE | TRUE | FALSE | TRUE | FALSE |
650 | TRUE | TRUE | FALSE | TRUE | FALSE |
660 | TRUE | TRUE | FALSE | TRUE | FALSE |
670 | TRUE | TRUE | FALSE | TRUE | FALSE |
675 | TRUE | TRUE | FALSE | TRUE | FALSE |
680 | TRUE | TRUE | FALSE | TRUE | FALSE |
689 | TRUE | TRUE | FALSE | TRUE | FALSE |
702 | TRUE | FALSE | FALSE | FALSE | FALSE |
703 | TRUE | FALSE | FALSE | FALSE | FALSE |
710 | TRUE | FALSE | FALSE | FALSE | FALSE |
720 | TRUE | FALSE | FALSE | FALSE | FALSE |
730 | TRUE | FALSE | FALSE | FALSE | FALSE |
740 | TRUE | FALSE | FALSE | FALSE | FALSE |
750 | TRUE | FALSE | FALSE | FALSE | FALSE |
800 | FALSE | FALSE | FALSE | FALSE | FALSE |
820 | FALSE | FALSE | FALSE | FALSE | FALSE |
830 | FALSE | FALSE | FALSE | FALSE | FALSE |
1504 | TRUE | FALSE | FALSE | FALSE | FALSE |
Here’s the first SWITCH function, which did not work:
Create column:
KPI_selection_groups =
SWITCH(TRUE(),
'PE TALOUS_TOSITERIVIT (2)'[TILIRYHMA] IN {300, 310, 320, 330, 340, 350},
"Liikevaihto",
'PE TALOUS_TOSITERIVIT (2)'[TILIRYHMA] IN {400, 411, 415, 420, 421, 422, 430},
"Muuttuvat kulut",
'PE TALOUS_TOSITERIVIT (2)'[TILIRYHMA] IN {450, 460, 462, 470, 500, 510, 520, 530, 540, 550, 610, 611, 620, 630, 640, 650, 660, 670, 675, 680, 689},
"Kiinteät kulut",
'PE TALOUS_TOSITERIVIT (2)'[TILIRYHMA] < 700,
"Liikevoitto",
'PE TALOUS_TOSITERIVIT (2)'[TILIRYHMA] < 800 || 'PE TALOUS_TOSITERIVIT (2)'[TILIRYHMA] >= 900,
"Tulos"
)
Expected result:
Using this column as a slicer, the
"Liikevaihto" should filter data for all rows having TILIRYHMA as 300, 310, 320, 330, 340, 350
"Muuttuvat kulut" should filter data for all rows having TILIRYHMA as 400, 411, 415, 420, 421, 422, 430
"Kiinteät kulut" should filter data for all rows having TILIRYHMA as 450, 460, 462, 470, 500, 510, 520, 530, 540, 550, 610, 611, 620, 630, 640, 650, 660, 670, 675, 680, 689
"Liikevoitto" should fitler data for all rows having TILIRYHMA less than 700 (including all the overlapping numbers from the 3 groups above)
"Tulos" should filter data for all rows having TILIRYHMA less than 800 or more or equal to 900. (So everything but numbers starting with 😎
But, for example “Tulos” shows only this:
Next I tried to use those separate columns for each group in the function, hoping that if I refer to different columns in SWITCH, the exclusion would not happen.
One example of the code to create those separate columns:
Liikevaihto T/F =
IF(
[TILIRYHMA] IN {300, 310, 320, 330, 340, 350}
, "True", "False")
Then creating a second SWITCH function using these different columns, but this does not work either:
Create column:
KPI_sel_columns =
SWITCH(TRUE(),
'PE TALOUS_TOSITERIVIT (2)'[Liikevaihto T/F]="True", "Liikevaihto",
'PE TALOUS_TOSITERIVIT (2)'[Muuttuvat kulut T/F]="True", "Muuttuvat kulut",
'PE TALOUS_TOSITERIVIT (2)'[Kiinteät kulut T/F] ="True", "Kiinteät kulut",
'PE TALOUS_TOSITERIVIT (2)'[Liikevoitto T/F] ="True", "Liikevoitto",
'PE TALOUS_TOSITERIVIT (2)'[Tulos T/F]="True", "Tulos"
)
Even in this case, for example the “Tulos” result contains only account numbers which are not part of the previous conditions.
So apparently SWITCH is not the way to go?
**
Is there any other way I could make this happen so that the customer could simply select the group name in a slicer and get fact data filtered by the appropriate account (TILIRYHMA) numbers?
Here's a link to the pbix file: Slicer_group_demo.pbix
I hope you can help me - I'd love to satisfy my customer needs 🙂.
Kind regards,
Minna
PS: I also tried to create a separate slicer table for the group names but couldn’t make it work.
Solved! Go to Solution.
It looks like you should change the structure of your data and unpivot all columns except TILIRYHMA (in Power Query, right clik on the TILIRYHMA header and select "Unpivot other columns"):
I've attached a sample PBIX file for you
Proud to be a Super User!
Paul on Linkedin.
It looks like you should change the structure of your data and unpivot all columns except TILIRYHMA (in Power Query, right clik on the TILIRYHMA header and select "Unpivot other columns"):
I've attached a sample PBIX file for you
Proud to be a Super User!
Paul on Linkedin.
@PaulDBrown , you are a true super user! 😎
Thank you for this solution - works perfectly! 😊
Cheers,
Minna
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
86 | |
85 | |
66 | |
49 |
User | Count |
---|---|
140 | |
113 | |
106 | |
64 | |
60 |