Create a custom slicer with groups of data containing overlapping values

[Anonymous]

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.

[PaulDBrown · Accepted 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

[Anonymous]

@PaulDBrown , you are a true super user! 😎

Thank you for this solution - works perfectly! 😊
Cheers,
Minna