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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
wribeiro
Helper I
Helper I

Slicer Selection

Hello everybory, I hope you are doing well. I have a simple question, and I would like your help.

 

I need to create a different slicer filter here, and I do not know how to handle it.
The following image represents my current model.

wribeiro_0-1682872171290.png

 

What do I want to do?

When I select Province A in my slicer, in fact, I wanna choose Provinces A,B,C,D

When I select Province B in my slicer, in fact, I wanna choose Provinces B,C,D,

and so on...
It is essential to say that I do not have a hierarchy here. My provinces are all on the same level.

wribeiro_1-1682872415728.png

My Power Bi sample is available here.

https://drive.google.com/file/d/1PvZxomKnPm-fjSii9j3uW0itK566QA5Y/view?usp=sharing

 

Thank you

1 ACCEPTED SOLUTION

See what you make of this:

provinces with calc group 

 

There is a calculation group configured via tabular editor:

-----------------------------------------------------------
-- Calculation Group: 'Province Selection Calulation Group'
-----------------------------------------------------------
CALCULATIONGROUP 'Province Selection Calulation Group'[Name]    Precedence = 1

    CALCULATIONITEM "Select Provinces" = 
        VAR MinIndex = MIN ( Province[Index] )
        
        RETURN
            CALCULATE(
                SELECTEDMEASURE(),
                REMOVEFILTERS( Province[Povince] ),
                Province[Index] >= MinIndex
            )

 

Basically the same measure but you don't need the calcualted table bit.

 

Then you just drop the calculation group into the visual level filter pane:

 

bcdobbs_0-1682878696142.png

 

This article explains more on creating them using tabular editor:
Introducing Calculation Groups - SQLBI

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

View solution in original post

8 REPLIES 8
ThxAlot
Super User
Super User

province.pbix

 

Simple enough, add an index column to Province table,

ThxAlot_0-1682875558174.png

ThxAlot_1-1682875610383.png



Expertise = List.Accumulate(


        {Days as from Today},


        {Skills and Knowledge},


        (Current, Everyday) => Current & Day.LeanAndPractise(Everyday)


)



I liked it, and it works. However,  I did not mention I have 53 measures, and in this case I would need to change all of them. 

I like @ThxAlot solution in DAX.

 

You could apply it to all measures by using a calculation group (configured via tabular editor). Just replace [val] with SELECTEDMEASURE(). Am away from a computer at the moment but can demo it later if you'd like.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Thank you @bcdobbs and @ThxAlot I really appreciate it. I have read about the tabular editor, and I have not used it (until now). If you could help me, it would be great. I am looking to create a "solution" that will be more generic, and my future clients could have an easy way to edit it. 

Scroll down the thread. Have sent a demo and a link to a SQLBI intro.

 

Calculation group editing I believe is coming to native power bi desktop at some point but not sure when. 

If you want it to be as easy as possible for end users my original suggestion of a bridge table might be a better way forward (although doubt the calc group would need changing)



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

See what you make of this:

provinces with calc group 

 

There is a calculation group configured via tabular editor:

-----------------------------------------------------------
-- Calculation Group: 'Province Selection Calulation Group'
-----------------------------------------------------------
CALCULATIONGROUP 'Province Selection Calulation Group'[Name]    Precedence = 1

    CALCULATIONITEM "Select Provinces" = 
        VAR MinIndex = MIN ( Province[Index] )
        
        RETURN
            CALCULATE(
                SELECTEDMEASURE(),
                REMOVEFILTERS( Province[Povince] ),
                Province[Index] >= MinIndex
            )

 

Basically the same measure but you don't need the calcualted table bit.

 

Then you just drop the calculation group into the visual level filter pane:

 

bcdobbs_0-1682878696142.png

 

This article explains more on creating them using tabular editor:
Introducing Calculation Groups - SQLBI

 



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Thank you @bcdobbs and @ThxAlot . Really appreciate it.

bcdobbs
Super User
Super User

I think the easiest way would be to use a bridge table with two columns:

Province Selection, Included

A, A

A, B

A, C

A, D

B, B

B, C

B, D

C, C

C, D

D, D

 

Then create a many to many relationship with filter in one direction from it to the City Table.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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