BUG with ALLSELECTED using a Hierarchical Slicer?

[jvirgi]

I think this is a bug, but let me know if its actually the desired behavior of ALLSELECTED.

I have 2 tables:

1 - table of locations  (COUNTRY, STATE, CITY)  [SLICE DATA ON THIS]

2 - Table of Places within those locations.  (CITY, PLACES)  [SHOW COUNTS OF THIS DATA]

They are disconnected, and i'm counting the number of places using a formula when the selected cities match.  

Using ALLSELECTED, when i select cities using the CITY level in the slicer, all works well.  If i Use the STATE level to pick all the cities,  ALLSELECTED reverts to using ALL cities, not just the ones selected by the STATE level.  Almost as if its seeing i chose a state, and so its thinking to show all cities..  Also it does this though if i choose all the cities within the state,  it then reverts to showing results for everything.

All is fixed when i use 'VALUES instead of ALLSELECTED, but i thought this wasnt the right behavior.

Thoughts?

Hierarchical Slicer With ALLSELECTED PBIX 

[v-luwang-msft · Accepted solution]

Hi @jvirgi ,

ALLSELECTED does not take into account the filter context. Its main purpose is that of retrieving a previously set shadow filter context. ALLSELECTED is very different from VALUES or DISTINCT. VALUES and DISTINCT always take into account the filter context, whereas ALLSELECTED does not. ALLSELECTED works on a column and checks whether that column is filtered by a shadow filter context, ignoring any cross-filter.You can learn more from the below article. It is very detailed.

the-definitive-guide-to-allselected : https://www.sqlbi.com/articles/the-definitive-guide-to-allselected/   

Wish it is helpful for you!

Best Regard

Lucien Wang

[AlexisOlson]

This is the measure you're using:

City_Places_Count_ALLSELECTED = 
CALCULATE(COUNTROWS('Places List'),
    FILTER('Places List','Places List'[City] in ALLSELECTED(Locations[City]))
)

Notice that you're using ALLSELECTED specifically on the Locations[City] column but your filter is actually on Locations[State]. Much like the difference between ISFILTERED and ISCROSSFILTERED, since the city is only indirectly filtered by state, there are no direct filters on Location[City], so ALLSELECTED(Locations[City]) behaves like ALL(Locations[City]).

These versions should work as you'd expect:

City_Places_Count_IN =
CALCULATE (
    COUNTROWS ( 'Places List' ),
    'Places List'[City] IN VALUES ( Locations[City] )
)

City_Places_Count_TREATAS =
CALCULATE (
    COUNTROWS ( 'Places List' ),
    TREATAS( VALUES( Locations[City] ), 'Places List'[City] )
)

Or you could set up a relationship between the tables and just use COUNTROWS ( 'Places List' ).

[jvirgi]

@AlexisOlson Thanks for the explanation.  That makes sense.  What doesn't make sense to me then is if you select all the cities directly within a State,  It behaves as if you directly filtered the state and shows ALL.

[AlexisOlson]

Yeah, they should probably make the slicer behavior more intuitive in this respect. It definitely causes unexpected behavior at times.