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 ) )
Hi @Tuan ,
How about this?
Stores (All Products) = CALCULATE ( DISTINCTCOUNT ( DimStore[Store] ), FILTER ( ALL ( DimProduct[Product]), [Net Sales] > 0 ) )
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
Stores (All Products) 2 = CALCULATE ( DISTINCTCOUNT ( DimStore[Store]), FILTER ( ALLEXCEPT ( 'FactData', 'DimStore'[Region], 'DimDate'[Week] ), [Net Sales] > 0 ) )
@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] )
This is a must watch for a message from Power BI!
Click here to read more about the December 2020 Updates!
Click here to read the latest blog and learn more about contributing to the Power BI blog!
Mark your calendars and join us for our next Power BI Dev Camp!.