Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tuan
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
Icey
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
    )
)

 

 

 

 

amitchandak
Super User
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]
)

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.