Whiteboard explanation helped me to understand more clearly, a humble suggestion is please do use that whiteboard with at least 2 examples with different data set. Thanks for helping us with great content
Excellent video! The subtle implications (e.g., when showing brand AND color in the visual) really help report authors understanding of potential results for future analysis (e.g., Allselected works at brand level, but not color). Again, brilliant as always - thanks!
I would like to say Thank you as you have used white board and explained in the best way a Teacher could please explain each concepts in the same way . I am ur fan now
Great One as always.I suppose on the last few minutes what Marco tried to say on ALLSELECTED is ,No matter what the selections are made onthe external slicers,you would be get the sum of { "Red ", "Green", "Blue" } ,thinking those have been selected externally. .If I select Red and Blue as in this example i would get the sum of those two colours not three colours.ALLSELECTED ,ALLWays Quite tricky.
If we add VALUES('Product'[Color]) as additional filter argument of Calculate in Trendy Colors 1 we get the same result as for Keepfilters, but ofcourse, using Keepfilters is better, more clear and simple. Thank you for great example and explanation
I've read the article on SQLBI and I came to know that if you use ALLSELECTED as a Table function with a Column it will dismiss all Filters and return the entire column if there is no Shadow Filter available. But here you are putting ALLSELECTED inside the FIlter as a Table Function and it is still behaving like a Calculate Modifier. Why? Is there anything I need to know?
You said that allselected sums all the color in the calculate funtion {green, red, blue} every time but when you open the contoso product we can see that only red and blue are summed, not green.
thank you for your great video. i am always confused. but this video help remind a of recap between allselected and keep filters!!! thank you very much
I think it may have helped if the Contoso colours shown @9:08 included the colour green. Since Green is not selected in the visual filter but is in the measure, see what value would be placed in the cell would make it easier to understand. So, if green is not in the visual filter, will the measure include it with keepfilters?
Good point, but it was intentional: indeed, the total is not including Green because it is not in the slicer. KEEPFILTERS would not add a color that is not included in the filter context, so there would be no difference in that case, too.
I learned many concept by SQLBI but got confused by this video. Its mentioned, ALLSELECTED override the other filter but I don't think this is correct. Filter (All(Product[Color], Product [color] in {"Red", "Green", "Blue"}) gives the same result as Filter (Allselected (Product[Color], Product [color] in {"Red", "Green", "Blue"}) Here its overriding the any other filter bcz of Product [color] in {"Red", "Green", "Blue"} which is mentioned after Allselected.
So is it like keep filters considers the context filters and the external filters. But all selected only considers the external filters and ignores the context filters?
Is KEEPFILTERS exactly the same as ALL when it's used as a CALCULATE modifier? I'm guessing that ALL can be used as both a CALCULATE modifier and as a standalone table function whereas KEEPFILTERS is just a CALCULATE modifier, is that correct? Also, KEEPFILTERS seems to make the code more intuitive and readable in terms of what it's doing. EDIT. I'm completely wrong, I think I'm referring to REMOVEFILTERS.
at 9:08 you are saying that allselected creates a filter on its own and disregards any external / internal filters. However if that would be the case, the total of 346k shouldnt be 436k as it was at 1:56 using simple Filter approach ? it seems to me that allselected takes the external filters (because it sees that color Green is not selected, therefore it doesnt add up to the initial 436k for Contoso line), however ignores the internal filters (or I would say a filter that comes from a row context), leading to the identical result for all colors within Contoso brand. Isnt that the case? If so then your statement at 9:00 that allselected sums all 3 colors "no matter what" would be incorrect as it clearly does not sum up color green. or maybe i got it totally wrong :D
I'm not sure I understand your question. However, you can find a complete explanation of ALLSELECTED here: www.sqlbi.com/articles/the-definitive-guide-to-allselected/
Hello, If we have calculated measures which are being used in other calculated measures..and if have many such calculated measures being used or being called in another calculated measures. What is the best practice to handle this. Because i have the such case and its consuming so much time in evaluating.
ALLSELECTED applies to filter context outside of the "visual", or outside of the last iterator - nested measures don't count. See this article for more details: www.sqlbi.com/articles/the-definitive-guide-to-allselected/
Didn't think of that... So KEEPFILTER does an operation similar to an INNER JOIN and blank out the others, while ALLSELECTED simply build the operation and override the existing filters... I'll have to take that one into account while building my DAX from now on...
You described the result you see in this example, but not how the two functions works internally - be careful, the result is different in other scenarios!
*Product [color] IN {Red, Blue Green}* Include only Red, Blue and Green colours, and ignore any filter of Product [color]. Trendy 2 *KEEPFILTERS ( Product [color] IN {Red, Blue Green} )* Applies any existing filter, and then apply other filter inside the bracket. Cascade. Intersect. *KEEPFILTERS ( typically another filter here)* Trendy 1 FILTER( ALLSELECTED( Product [color] ), Product [color] IN {Red, Blue Green} ) First, include whatever Product [color] selected in slicer, and ignore any other filter, then include Red, Blue, Green with IN.
Thank you..what's the point of writing allselected filter if the Product [color] in {Red,Blue,Green} include only these colors and ignore any filter on Product[color] We will get this result without Allselected as well
Whiteboard explanation helped me to understand more clearly, a humble suggestion is please do use that whiteboard with at least 2 examples with different data set. Thanks for helping us with great content
Excellent video!
The subtle implications (e.g., when showing brand AND color in the visual) really help report authors understanding of potential results for future analysis (e.g., Allselected works at brand level, but not color).
Again, brilliant as always - thanks!
I would like to say Thank you as you have used white board and explained in the best way a Teacher could please explain each concepts in the same way . I am ur fan now
Great One as always.I suppose on the last few minutes what Marco tried to say on ALLSELECTED is ,No matter what the selections are made onthe external slicers,you would be get the sum of { "Red ", "Green", "Blue" } ,thinking those have been selected externally. .If I select Red and Blue as in this example i would get the sum of those two colours not three colours.ALLSELECTED ,ALLWays Quite tricky.
This explanation is probably the best that I heard about this function. Thank you very much!
After watching half of this video I was already enlightened, thank you so much, I did struggle to understand the difference :D
Great explanation! This has helped me understand the two options. Thanks for that.
It was your first video i watched and liked it. Your explanation was so smooth and understandable
Following up the matrix explanation with the Whiteboard one helped, thanks. I think I'm (finally) starting to reallly understand KEEPFILTERS.
If we add VALUES('Product'[Color]) as additional filter argument of Calculate in Trendy Colors 1 we get the same result as for Keepfilters, but ofcourse, using Keepfilters is better, more clear and simple. Thank you for great example and explanation
Nice video as always, clear and concise! Thanks Marco!
Both of you are the best.No other words :)
I've read the article on SQLBI and I came to know that if you use ALLSELECTED as a Table function with a Column it will dismiss all Filters and return the entire column if there is no Shadow Filter available. But here you are putting ALLSELECTED inside the FIlter as a Table Function and it is still behaving like a Calculate Modifier. Why?
Is there anything I need to know?
Thank you very much for your great explanations, I'm a fan now)
Great explanation. Thank you so much.
You said that allselected sums all the color in the calculate funtion {green, red, blue} every time but when you open the contoso product we can see that only red and blue are summed, not green.
Thanks, Marco Russo
Thank you for this video! An a DAX-unrelated note.. What tool are you using to zoom/draw the boxes live? Thank you.
Zoom It: docs.microsoft.com/en-us/sysinternals/downloads/zoomit
thank you for your great video. i am always confused. but this video help remind a of recap between allselected and keep filters!!! thank you very much
Very nice explanation. Thanks.
Thank you very much, this is really clear, very well explain, thx!
I think it may have helped if the Contoso colours shown @9:08 included the colour green. Since Green is not selected in the visual filter but is in the measure, see what value would be placed in the cell would make it easier to understand. So, if green is not in the visual filter, will the measure include it with keepfilters?
Good point, but it was intentional: indeed, the total is not including Green because it is not in the slicer. KEEPFILTERS would not add a color that is not included in the filter context, so there would be no difference in that case, too.
Well explained! Thanks a lot!
Nicely done. Thank you!
Thank you. If we use keepfilters () in trendy colours 1 measure, then results will be identical to the 2nd measure?
Is it possible to download the PowerBI file used? Thank you very much!
Yes, from this page: www.sqlbi.com/tv/differences-between-allselected-and-keepfilters-unplugged-33/
Great video 👍
thank you
I learned many concept by SQLBI but got confused by this video. Its mentioned, ALLSELECTED override the other filter but I don't think this is correct.
Filter (All(Product[Color], Product [color] in {"Red", "Green", "Blue"}) gives the same result as Filter (Allselected (Product[Color], Product [color] in {"Red", "Green", "Blue"})
Here its overriding the any other filter bcz of Product [color] in {"Red", "Green", "Blue"} which is mentioned after Allselected.
So is it like keep filters considers the context filters and the external filters. But all selected only considers the external filters and ignores the context filters?
Thanks
Is KEEPFILTERS exactly the same as ALL when it's used as a CALCULATE modifier? I'm guessing that ALL can be used as both a CALCULATE modifier and as a standalone table function whereas KEEPFILTERS is just a CALCULATE modifier, is that correct? Also, KEEPFILTERS seems to make the code more intuitive and readable in terms of what it's doing.
EDIT. I'm completely wrong, I think I'm referring to REMOVEFILTERS.
Read this article/video and related articles: www.sqlbi.com/articles/when-to-use-keepfilters-over-iterators/
at 9:08 you are saying that allselected creates a filter on its own and disregards any external / internal filters.
However if that would be the case, the total of 346k shouldnt be 436k as it was at 1:56 using simple Filter approach ?
it seems to me that allselected takes the external filters (because it sees that color Green is not selected, therefore it doesnt add up to the initial 436k for Contoso line), however ignores the internal filters (or I would say a filter that comes from a row context), leading to the identical result for all colors within Contoso brand.
Isnt that the case?
If so then your statement at 9:00 that allselected sums all 3 colors "no matter what" would be incorrect as it clearly does not sum up color green.
or maybe i got it totally wrong :D
I'm not sure I understand your question.
However, you can find a complete explanation of ALLSELECTED here:
www.sqlbi.com/articles/the-definitive-guide-to-allselected/
Hello,
If we have calculated measures which are being used in other calculated measures..and if have many such calculated measures being used or being called in another calculated measures. What is the best practice to handle this.
Because i have the such case and its consuming so much time in evaluating.
ALLSELECTED applies to filter context outside of the "visual", or outside of the last iterator - nested measures don't count.
See this article for more details: www.sqlbi.com/articles/the-definitive-guide-to-allselected/
Excelent!!!
WHAT ABOUT THE FILTERS ON THE RIGHT-HAND-SIDE DRAWER???? HOW DO YOU RESPECT THOSE??
Didn't think of that... So KEEPFILTER does an operation similar to an INNER JOIN and blank out the others, while ALLSELECTED simply build the operation and override the existing filters... I'll have to take that one into account while building my DAX from now on...
You described the result you see in this example, but not how the two functions works internally - be careful, the result is different in other scenarios!
*Product [color] IN {Red, Blue Green}*
Include only Red, Blue and Green colours, and ignore any filter of Product [color].
Trendy 2
*KEEPFILTERS ( Product [color] IN {Red, Blue Green} )*
Applies any existing filter, and then apply other filter inside the bracket. Cascade. Intersect.
*KEEPFILTERS ( typically another filter here)*
Trendy 1
FILTER(
ALLSELECTED( Product [color] ),
Product [color] IN {Red, Blue Green} )
First, include whatever Product [color] selected in slicer, and ignore any other filter, then include Red, Blue, Green with IN.
Thank you..what's the point of writing allselected filter if the Product [color] in {Red,Blue,Green} include only these colors and ignore any filter on Product[color]
We will get this result without Allselected as well