DISTINCTCOUNT ignoring filter context

[ajmonster]

Here's my data model:

Stores 1-* Sales

Each Store is associated with a Market, Zone, and ZIP Code. My goal is to return the associated Store count for each Market, Zone, and ZIP Code for a specific store. So in practice, when a user filters to a specific Store Number, I will be able to return the count of stores in the Market, Zone, and ZIP Code associated with the filtered store. My Zone measure is as follows and works as expected:

Store Count, Zone =
CALCULATE(
    DISTINCTCOUNT('Sales'[Store Number]), 
    ALLEXCEPT( 
        'Store Key', 
        'Store Key'[Zone] 
    ), 
    NOT(ISBLANK('Sales'[$ Sales])) 
)

However, when I use the same measure but replace Zone with Market or Zip Code, the measure does not work as intended and instead returns the full count of all the stores. Also, the Market and Zip Code measures work as intended when there are Market and Zip Code contexts present in the visual; if I bring in the measure to a visual that has the Market/Zip Code dimensions, I get the value I desire.

I'm not sure what's going on at this point and why only the Zone measure works. TIA.

[v-gizhi-msft]

Hi,

Please try this(etc. for [Market]):

Store Count, Zone =
CALCULATE (
    DISTINCTCOUNT ( 'Sales'[Store Number] ),
    FILTER (
        ALLSELECTED ( 'Store Key' ),
        'Store Key'[Market] IN FILTERS ( 'Store Key'[Market] )
    ),
    NOT ( ISBLANK ( 'Sales'[$ Sales] ) )
)

Hope this helps.

Best Regards,

Giotto

[az38]

Hi @ajmonster 

at first sight I see a couple of options, depending on your data model and use cases

Store Count, Zone =
CALCULATE(
    DISTINCTCOUNT('Sales'[Store Number]), 
    ALLEXCEPT( 
        'Store Key', 
        'Store Key'[Zone],
        'Store Key'[Market],
        'Store Key'[ZIP Code] 
    ), 
    NOT(ISBLANK('Sales'[$ Sales])) 
)

or

Store Count, Zone =
CALCULATE(
    DISTINCTCOUNT('Sales'[Store Number]), 
    ALLSELECTED( 
        'Store Key' 
     ), 
    NOT(ISBLANK('Sales'[$ Sales])) 
)