Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
Solved! Go to 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})
)
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.
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.
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
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})
)
Thanx so much for this information
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.
User | Count |
---|---|
21 | |
19 | |
12 | |
9 | |
7 |
User | Count |
---|---|
30 | |
27 | |
14 | |
13 | |
10 |