Return list based on if filtered

[SteveCampbell]

Place                Region

place 1                 A

place 2                 A

place 3                 B

place 4                 B

Hello, I need some help with a table such as the above. I have a slicer on region. If Region is not filtered, I want a list of Regions, but if it is filtered I want a list of Places for that region.

E.G

Filter :

ALL

Return:

Region A

Region B

Filter:

Region A

Return:

Place 1

Pleace 2

Thank you

[Sean]

@SteveCampbell

Could the returns be in this format - concatenated on 1 line?

No Filter

Region A, Region B

Region Filter

Place 1, Place 2

[SteveCampbell]

No - I need a list.

My thought is to create a column with some type of IF operator.

I can create a filter easily:

ISFILTERED('Table'[REGION])

but if I encorporate this into a calculated column it always takes into account the row context and returns the same value whether the filter is on or not

[Sean]

@SteveCampbell

This Measure will concatenate and order a-z you results

Regions/Places Measure =
IF (
    ISFILTERED ( Table[Region] ),
    CONCATENATEX ( VALUES ( Table[Place] ), Table[Place], ", ", Table[Place], ASC ),
    CONCATENATEX (
        VALUES ( Table[Region] ),
        Table[Region],
        ", ",
        Table[Region], ASC
    )
)

Obviously if you have too many of each regions and places this is not the best approach...

[SteveCampbell]

I am looking for rows.

for example a calculated column:

 if ( ISFILTERED (REGION) = TRUE,

      [PLACE],

      [REGION]

   )

something like that, but I want it to not take into account the row context, as this always returns the region

[dkay84_PowerBI]

When you say "list" what do you mean?  Do you want the values for Region or Place to appear in a table? Or are these values in slicer (ie a hierarchy slicer)?

[SteveCampbell]

A table.

if there is a filter on region, display the place, otherwise display the regions.

No filter:

Filter on:

[v-sihou-msft · Accepted solution]

@SteveCampbell

No, it's not possible. Once your put the [Region], the filter will be delegated to [Region] directly so that you will see it always show [Region] field. If you don't want to take it into row context, you need to create measure. However, a measure can never expanded those related Places into multiple records, they must be aggregated. So you can only have both column appeared in visuals or creating hierarchy to drilldown to place level.

Regards,