Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
Anonymous
Not applicable

Create a custom slicer with groups of data containing overlapping values

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)

TILIRYHMATulos  T/FLiikevoitto T/FLiikevaihto T/FKiinteat kulut T/FMuuttuvat kulut T/F
104TRUETRUEFALSEFALSEFALSE
106TRUETRUEFALSEFALSEFALSE
110TRUETRUEFALSEFALSEFALSE
121TRUETRUEFALSEFALSEFALSE
122TRUETRUEFALSEFALSEFALSE
123TRUETRUEFALSEFALSEFALSE
124TRUETRUEFALSEFALSEFALSE
125TRUETRUEFALSEFALSEFALSE
130TRUETRUEFALSEFALSEFALSE
140TRUETRUEFALSEFALSEFALSE
141TRUETRUEFALSEFALSEFALSE
142TRUETRUEFALSEFALSEFALSE
144TRUETRUEFALSEFALSEFALSE
145TRUETRUEFALSEFALSEFALSE
150TRUETRUEFALSEFALSEFALSE
152TRUETRUEFALSEFALSEFALSE
153TRUETRUEFALSEFALSEFALSE
154TRUETRUEFALSEFALSEFALSE
155TRUETRUEFALSEFALSEFALSE
160TRUETRUEFALSEFALSEFALSE
161TRUETRUEFALSEFALSEFALSE
162TRUETRUEFALSEFALSEFALSE
163TRUETRUEFALSEFALSEFALSE
166TRUETRUEFALSEFALSEFALSE
180TRUETRUEFALSEFALSEFALSE
190TRUETRUEFALSEFALSEFALSE
200TRUETRUEFALSEFALSEFALSE
201TRUETRUEFALSEFALSEFALSE
203TRUETRUEFALSEFALSEFALSE
205TRUETRUEFALSEFALSEFALSE
220TRUETRUEFALSEFALSEFALSE
232TRUETRUEFALSEFALSEFALSE
250TRUETRUEFALSEFALSEFALSE
253TRUETRUEFALSEFALSEFALSE
258TRUETRUEFALSEFALSEFALSE
260TRUETRUEFALSEFALSEFALSE
273TRUETRUEFALSEFALSEFALSE
275TRUETRUEFALSEFALSEFALSE
276TRUETRUEFALSEFALSEFALSE
278TRUETRUEFALSEFALSEFALSE
279TRUETRUEFALSEFALSEFALSE
280TRUETRUEFALSEFALSEFALSE
282TRUETRUEFALSEFALSEFALSE
300TRUETRUETRUEFALSEFALSE
310TRUETRUETRUEFALSEFALSE
320TRUETRUETRUEFALSEFALSE
330TRUETRUETRUEFALSEFALSE
340TRUETRUETRUEFALSEFALSE
350TRUETRUETRUEFALSEFALSE
360TRUETRUEFALSEFALSEFALSE
390TRUETRUEFALSEFALSEFALSE
400TRUETRUEFALSEFALSETRUE
411TRUETRUEFALSEFALSETRUE
415TRUETRUEFALSEFALSETRUE
420TRUETRUEFALSEFALSETRUE
421TRUETRUEFALSEFALSETRUE
422TRUETRUEFALSEFALSETRUE
430TRUETRUEFALSEFALSETRUE
450TRUETRUEFALSETRUEFALSE
460TRUETRUEFALSETRUEFALSE
462TRUETRUEFALSETRUEFALSE
470TRUETRUEFALSETRUEFALSE
500TRUETRUEFALSETRUEFALSE
510TRUETRUEFALSETRUEFALSE
520TRUETRUEFALSETRUEFALSE
530TRUETRUEFALSETRUEFALSE
540TRUETRUEFALSETRUEFALSE
550TRUETRUEFALSETRUEFALSE
600TRUETRUEFALSEFALSEFALSE
610TRUETRUEFALSETRUEFALSE
611TRUETRUEFALSETRUEFALSE
620TRUETRUEFALSETRUEFALSE
630TRUETRUEFALSETRUEFALSE
640TRUETRUEFALSETRUEFALSE
650TRUETRUEFALSETRUEFALSE
660TRUETRUEFALSETRUEFALSE
670TRUETRUEFALSETRUEFALSE
675TRUETRUEFALSETRUEFALSE
680TRUETRUEFALSETRUEFALSE
689TRUETRUEFALSETRUEFALSE
702TRUEFALSEFALSEFALSEFALSE
703TRUEFALSEFALSEFALSEFALSE
710TRUEFALSEFALSEFALSEFALSE
720TRUEFALSEFALSEFALSEFALSE
730TRUEFALSEFALSEFALSEFALSE
740TRUEFALSEFALSEFALSEFALSE
750TRUEFALSEFALSEFALSEFALSE
800FALSEFALSEFALSEFALSEFALSE
820FALSEFALSEFALSEFALSEFALSE
830FALSEFALSEFALSEFALSEFALSE
1504TRUEFALSEFALSEFALSEFALSE

 

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:
Slicer_group_demo_incorrect_filtering.png

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.

1 ACCEPTED SOLUTION
PaulDBrown
Community Champion
Community Champion

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"):

Unpivot.gif

Res.gif

I've attached a sample PBIX file for you





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

2 REPLIES 2
PaulDBrown
Community Champion
Community Champion

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"):

Unpivot.gif

Res.gif

I've attached a sample PBIX file for you





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Anonymous
Not applicable

@PaulDBrown , you are a true super user! 😎

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

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.