cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Measure - 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 6
Community Support
Community Support

Hi @Tuan ,

 

How about this?

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

produc.PNG

 

 

Best Regards,

Icey

 

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

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

 

Tuan_0-1599090324971.png

 

 

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

 

 

 

 

Super User IV
Super User IV

@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]
)


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors