topic Re: Best way to exclude values with dax in DAX Commands and Tips

Best way to exclude values with dax

Dimitris_Kats — Fri, 01 Oct 2021 07:22:54 GMT

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

Re: Best way to exclude values with dax

Payeras_BI — Fri, 01 Oct 2021 08:51:20 GMT

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}) )

Re: Best way to exclude values with dax

Dimitris_Kats — Fri, 01 Oct 2021 09:07:29 GMT

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.

Re: Best way to exclude values with dax

Payeras_BI — Fri, 01 Oct 2021 11:35:06 GMT

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.

Re: Best way to exclude values with dax

Dimitris_Kats — Fri, 01 Oct 2021 14:04:58 GMT

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

Re: Best way to exclude values with dax

Anonymous — Sat, 12 Aug 2023 20:30:41 GMT

Thanx so much for this information