Forum Discussion

ajmonster's avatar
ajmonster
Icon for Helper II rankHelper II
6 years ago

DISTINCTCOUNT ignoring filter context

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.

2 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion

    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])) 
    )
  • v-gizhi-msft's avatar
    v-gizhi-msft
    Icon for Community Support rankCommunity Support

    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