Best way to exclude values with dax

[Dimitris_Kats]

Hello,

I am new to dax and i would really like your advice.
I have a product table with products assigned to categories.
We decided to exclude some products of the category 1 and i needed a measure to calculate only the products that were not excluded and another measure for only the products we excluded.
I managed to do it, but i was thinking there is a better and more efficient way to write that measure.

This is my table:

PRODUCT ID	PRODUCT CATEGORY 1	PRODUCT CATEGORY 2	PRODUCT CATEGORY 3	PRODUCT CATEGORY 4	PRODUCT CATEGORY 5
1000	1
1001	1
1002	1
1003	1
1004		1
1005		1
1006
1007	1
1008	1
1009	1
1010			1
1011				1
1012					1
1013	1
1014	1
1015		1
1016		1
1017		1
1018		1
1019			1
1020				1
1021	1
1022					1
1023	1
1024	1

And these are my measures: 

Correct Product Category = CALCULATE(SUM(PRODUCTS[PRODUCT CATEGORY 1]), FILTER(PRODUCTS,PRODUCTS[PRODUCT ID] <> 1000), FILTER(PRODUCTS,PRODUCTS[PRODUCT ID] <> 1001), FILTER(PRODUCTS,PRODUCTS[PRODUCT ID] <> 1007) , FILTER(PRODUCTS,PRODUCTS[PRODUCT ID] <> 1014) , FILTER(PRODUCTS,PRODUCTS[PRODUCT ID] <> 1023))

Product Exceptions = CALCULATE(SUM(PRODUCTS[PRODUCT CATEGORY 1]), FILTER(PRODUCTS, PRODUCTS[PRODUCT ID] = 1000 ||PRODUCTS[PRODUCT ID] = 1001|| PRODUCTS[PRODUCT ID] = 1007||PRODUCTS[PRODUCT ID] = 1014 ||PRODUCTS[PRODUCT ID] = 1023))

I would really appreciate your suggestions.

Thank you in advance 

[Payeras_BI · Accepted solution]

Hi @Dimitris_Kats,

An alternative to these two measures:

Correct Product Category ii = 
CALCULATE (
    SUM ( PRODUCTS[PRODUCT CATEGORY 1] ),
    KEEPFILTERS(NOT PRODUCTS[PRODUCT ID] IN {1000,1001,1007,1014,1023})
)

Product exceptions ii = 
CALCULATE (
    SUM ( PRODUCTS[PRODUCT CATEGORY 1] ),
    KEEPFILTERS(PRODUCTS[PRODUCT ID] IN {1000,1001,1007,1014,1023})
)

[Payeras_BI · Accepted solution]

Hi again @Dimitris_Kats,

Thanks to you for your kind words. 😉

Yes, you are right, I thought the operators NOT and IN would help to simplify the code.

Regarding using the KEEPFILTERS modifier, it was because I assumed you could use the measures in a table / matrix visualization like the one below:

If so, you want to keep the outer filter on Product ID and intersect it with the filter provided in your Calculate statement.

Without removing KEEPFILTERS you would get this:

I hope this helps you.

[Dimitris_Kats]

That was really helpful, i totaly understand what you mean and why you used KEEPFILTERS.

Thank you very much again for the detailed explanation 

Your help is greatly appreciated 

[Payeras_BI · Accepted solution]

Hi @Dimitris_Kats,

An alternative to these two measures:

Correct Product Category ii = 
CALCULATE (
    SUM ( PRODUCTS[PRODUCT CATEGORY 1] ),
    KEEPFILTERS(NOT PRODUCTS[PRODUCT ID] IN {1000,1001,1007,1014,1023})
)

Product exceptions ii = 
CALCULATE (
    SUM ( PRODUCTS[PRODUCT CATEGORY 1] ),
    KEEPFILTERS(PRODUCTS[PRODUCT ID] IN {1000,1001,1007,1014,1023})
)

[537849]

Thanx so much for this information

[Dimitris_Kats]

Thank you very much.

Both of them are working perfectly.
Is it too much to ask to explain me a little bit how your measures works?

You added keepfilters because you want to preserve the filters on products id right?
And you use NOT & IN to exclude the ID's. Is that correct??
Thank you very much again for replying to me so fast and of course for the amazing measures.