Slicer Selection

[wribeiro]

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.

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.

My Power Bi sample is available here.

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

Thank you

[ThxAlot]

province.pbix

Simple enough, add an index column to Province table,

[wribeiro]

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. 

[bcdobbs]

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.

[wribeiro]

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. 

[bcdobbs]

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)

[bcdobbs · 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:

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

[wribeiro]

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

[bcdobbs]

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.