Measure - Excluding filter from Filter Pane

[Tuan]

Hello,

I can't seem to figure this one out. I'm trying to use a measure that excludes a filter on the Filter Pane. I'm creating product cards in which I select one product at a time.  I'm trying to calcualte the Velocity of each product by the total stores in which products are. I actually only one to exclude the Product filter which is why I use Allexcept.

Stores All Products = 
CALCULATE (
    DISTINCTCOUNT ( Store_List[Store Name] ),
    FILTER (
        ALLEXCEPT (
            'Daily Data',
            Store_List[WFM REGION],
            Store_List[STORE NAME],
            'Calendar'[Date]
        ),
        'Daily Data'[Net_Sales] > 0
    )
)

[Icey]

Hi @Tuan ,

How about this?

Stores (All Products) = 
CALCULATE (
    DISTINCTCOUNT ( DimStore[Store] ),
    FILTER ( ALL ( DimProduct[Product]), [Net Sales] > 0 )
)

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Tuan]

That doesn't quite work as it gives me Store count for zero sales weeks, I believe the net sales >0 part of the formula is not working correction as shown by the picture below. I got it to work using the formula below. For some reason it doesn't work on my actual data, maybe due to my connections, which im still trying to figure out.

New link with my live calendar

https://drive.google.com/file/d/1hxEgV-ZYCWUJ2OBkoqiWDGb-UkBsasph/view?usp=sharing

Stores (All Products) 2 = 
CALCULATE (
    DISTINCTCOUNT ( DimStore[Store]),
    FILTER (
        ALLEXCEPT (
            'FactData',
            'DimStore'[Region],
            'DimDate'[Week]
        ),
        [Net Sales] > 0
    )
)

[amitchandak]

@Tuan , Try like

  Stores All Products = 
Sumx (filter(Summarize(  'Daily Data',
            Store_List[WFM REGION],
            Store_List[STORE NAME],
            'Calendar'[Date] , "_1",
    DISTINCTCOUNT ( Store_List[Store Name] ) ),
        [_1] > 0
    ),[_1]
)

[Tuan]

Giving me a incorrect number. Still trying to figure it out. Main issue is that my date filter is not being corrupted excluded.

[amitchandak]

@Tuan , Can you share sample data and sample output in table format?

[Tuan]

https://drive.google.com/file/d/1tm4zTnC48siLjSESfQcUjrtP56k2VsLO/view?usp=sharing

Here's an example file with details.