All function does not ignore my filter

DAX DAX Calculations
1

Hi, again, I have one more question about the function ALL. what I am trying to achieve is to calculate the total OOS days (out of stock) days regardless my filter selection. It is kind of YTD OOS days. I used All in my DAX, but it did not work as I expected. Could someone please help me take a look of the syntax to see what I did wrong? Basically the YTD OOS measure should be changed for a same product when I change the date slice.

image
image1137×273 14.7 KB

Thanks!

Opportunity_test.pbix (95.3 KB)

Mike

3

@Mike,

Give this a go:

YTD OOS ALL = 
CALCULATE(
    DISTINCTCOUNT(  'F_Instock_tbl'[Date]  ),
    F_Instock_tbl[OOS-Flag] = 1,
    REMOVEFILTERS( 'F_Instock_tbl' ),
    VALUES( D_Product_tbl[Produc_ID] )
)

image
image1065×239 7.79 KB

  • Brian
4

Thanks, @BrianJ.

That works, however, it significantly impacts the performance of the dashboard. I am wondering if there is any way to do it.

Mike

5

@Mike,

See if this runs any faster. If not, we may need to call in the big guns on optimization (@AntrikshSharma).

YTD OOS ALL Rev = 
CALCULATE(
    COUNTROWS(
        VALUES('F_Instock_tbl'[Date])
    ),
    F_Instock_tbl[OOS-Flag] = 1,
    REMOVEFILTERS( 'F_Instock_tbl' ),
    VALUES( D_Product_tbl[Produc_ID] )
)
  • Brian
2 Likes
6

Hi, @BrianJ, it seems getting better. Thanks a lot. I will ask for more assistance if the performance becomes a real pain.

Thanks again!

Mike

7

@Mike,

Great - glad to help.

Even if it’s not a problem, you may want to submit this to the Optimization Lab for learning purposes.

  • Brian
8

@BrianJ, thanks Brain. That will be awesome. Did not know we have submit a ticket to Optimization Lab for learning. Will definitely do it.

Cheers,

Mike

1 Like