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
SteveCampbell
Memorable Member
Memorable Member

Return list based on if filtered

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



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



1 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,

View solution in original post

7 REPLIES 7
Sean
Community Champion
Community Champion

@SteveCampbell

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

No Filter

Region A, Region B

Region Filter

Place 1, Place 2

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



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



@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...

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



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



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)?

A table.

 

 

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

 

No filter:

aa.png

Filter on:

 

 

bb.png



Did I answer your question? Mark my post as a solution! Proud to be a Super User!


Connect with me!
Stay up to date on  
Read my blogs on  



@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,

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.