Dynamic filter range

[Anonymous]

Hello, I need some help please.

i have two tables:
City

and sales:

when i select some city. i want to see the the top 3 cities that the sales is +500 then my current filter city,
for example:

if I select Yavne, i Want to see:

Modiin 200
Tel Aviv 500
Rehovot 700
here is my test file:
https://file.io/MaQvddQ8nmUk
can some one help me please? 

Thank you!

[Anonymous]

Hi @Anonymous ,

It seems that the shared file has been deleted...

https://file.io/MaQvddQ8nmUk

Best Regards

[Anonymous · Accepted solution]

Hi @Anonymous ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create another city dimension table(DO NOT create any relationship with other table)

Table = VALUES(City[CityName])

2. Create a measure as below to judge which city fulfill the conditions

Flag =
VAR _cities =
    ALLSELECTED ( 'Table'[CityName] )
VAR _selcids =
    SELECTEDVALUE ( 'City'[CityID] )
VAR _cids =
    CALCULATETABLE (
        VALUES ( 'City'[CityID] ),
        FILTER ( ALLSELECTED ( 'City' ), 'City'[CityName] IN _cities )
    )
VAR _sales =
    CALCULATE (
        SUM ( 'Sales'[Column1] ),
        FILTER ( ALLSELECTED ( 'Sales' ), 'Sales'[CityID] IN _cids )
    )
VAR _tab =
    SUMMARIZE (
        'Sales',
        'Sales'[CityID],
        "@sales",
            CALCULATE (
                SUM ( 'Sales'[Column1] ),
                FILTER (
                    ALLSELECTED ( 'Sales' ),
                    'Sales'[CityID] = EARLIER ( 'Sales'[CityID] )
                )
            )
    )
RETURN
    IF (
        _selcids
            = MAXX (
                FILTER ( _tab, [@sales] >= _sales && [@sales] <= _sales + 500 ),
                [CityID]
            ),
        1,
        0
    )

3. Create a table visual and apply a visual-level filter with the condition(Flag is 1)

Best Regards

[Anonymous]

Thank you very much, I really apprciate it.
It works great but if i need to slice the CityName from my origial dimension? I have more visuals in the same dashboard so I need to slice the other visuals as well.

Thanks again!

[Anonymous]

Hi @Anonymous ,

I'm afraid that it can't be achieved when you apply the CityName from your original dimension table. If you applied that field on the slicer, the visual will only display the data with the selected city not display the others. The possible workaround may be this:

1. Keep the current slicer

2. Create another slicer which applied the field [CityName] of original dimension table to filter the other visuals

3. Important: please cancel the interaction  between the new slicer and the original table visual just as below screenshot.

Change how visuals interact in a report - Power BI | Microsoft Learn

Best Regards

[Anonymous]

Ok,Thank you very much!

[Anonymous]

I mean that the range is between my current sales and my current sales + 500.