Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Dynamic filter range

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 filte...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        Ok,Thank you very much!