Forum Discussion
Helper IIIMeasure - Excluding filter from Filter Pane
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
)
)
6 Replies
- amitchandak
Super User
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] )
TuanGiving 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?
- Icey
Community Support
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 ) )
