Hello Sir, I am from India and love your way of teaching because it is always Practical and Concise. I watch your videos on daily basis whenever I Come across any difficulties in understanding concepts of Power BI especially DAX. Thanks for teaching all these valuable concepts for free of cost.
Thank you so much to both of you for continuing to educate us on proper DAX usage. I literally pick out something useful on each of these videos, and am often able to apply it in my next work project.
Thank you very much for this excellent tutorial! I'm already tweaking some measures that I made before knowing this powerful info. You make it SO EASY to learn. :-)
This really helped me get over a problem with circular references and keeping multiple filters. Calculate worked great until I added in a DAX table with a column referencing a measure with calculate in it. Not sure if its the best way but I got a solution using the verbose use of FILTER & SUMMARIZE. We will see if I have cracked it 🙂
Hi Alberto, great explanations as always! Thank you. Could you kindly explain use of KEEPFILTERS in a more complex example used in your book (Definitive Guide), SalesOfFirstYearCustomers (page 385), where you use it in CALCULATE as a modifier and wrap it around table derived from VALUES(Sales[CustomerKey])? You briefly mentioned that purpose of KEEPFILTERS there is to make it possible to filter customers, say by country - but after running so many scenarios, I couldn't find a realistic one where it could really be used to make a difference in Matrix. Also should it be maybe better to use table SUMMARIZE(Sales, Customer[CustomerKey]) instead of VALUES(Sales[CustomerKey]) in CALCULATE so as to filter by dimension table column?
Great video, Alberto! Please keep doing these common DAX CALCULATEvideos I've been doing DAX for years but love to keep the fundamentals fresh in my mind. Quick question: Instead of using SUMMARIZE, can't you just use VALUES twice in the filter arguments of CALCULATE? Does the following expression work for [Only Red Contoso VALUES] measure instead of using the SUMMARIZE function? Only Red Contoso VALUES = CALCULATE ( [Sales Amount], AND ( 'Product'[Color] = "Red", 'Product'[Brand] = "Contoso" ), VALUES ( 'Product'[Color] ), VALUES ( 'Product'[Brand] ) )
It's not the same - in general, you could have arbitrary combinations of color and brand selected, so if the user selected (Green+Contoso) and (Red+Litware) using a hierarchical slicer, the result with VALUES would show Red+Contoso, which wouldn't be included by using the SUMMARIZE version.
Hi, thanks, I think I understand a bit more re SUMMARIZE, am i right in thinking that SUMMARIZE ie SUMMARIZE( Table1, Table1[Color]) , produces a table in the current filter context, winch is why, CALCULATE( [TotalSales],FILTER( SUMMARIZE( Table1, Table1[Color]), Table1[Color] = "red")) does not need Keep Filters, I only found this out while trying it instead of my usual CROSSJON to create a table of multiple columns to feed to Filter. Oh, Happy New Year. Perhaps I should make resolution, to finally sit and read your book properly.
I'm trying to do the always version but keep getting the value only on the "FirstDate1" any idea why and how can I make it to show this everywhere? My Code: FirstValue = Var FirstDate1 = MINX(ALLSELECTED(Merge1[Dates]),Merge1[Dates]) Var PrvniCena = CALCULATE([mCena weighted], FILTER(ALL('Merge1'[Dates]), Merge1[Dates] = FirstDate1)) RETURN PrvniCena
Hi and thanks for the info. I tried it in ma test file and it's now working properly - formula for "always red" gives me the result as in "only red" (instead of showing all values it shows only one) and to get this result i need to use ALL with FILTER (as in "only red values ext") . Yeah, it's working, but the opposite way. Any ideas why this might happen?
Got the same issue yesterday and was stumped for a while.Finally realised the culprit was the sorting of a column in the visual by another column of the same table.Then I remembered Alberto had posted a video on that some while ago. Can’t paste the link for some reason now, if you ‘d like to check it out you can search for: “side effects of sort by column”.
Brilliant as always 😀 how many likes can I click? I have a question: What if the columns are in different tables. I guess KEEPFILTERS has no problem with that? What about VALUES? Shall we use CROSSJOIN?
![Lp. Сердце Вселенной #60 РОЖДЕНИЕ ЛОЛОЛОШКИ [Финал] • Майнкрафт](http://i.ytimg.com/vi/YoR0pAV9FVQ/mqdefault.jpg)
Absoutelly a genius !, not for the knowledge itself (that also), but for the way and style you use to teach it to people. Thank you v. much.
Thanks for sharing, not only for the content but also for the way Alberto explains it. I love it. Grazie mille!
Hello Sir,
I am from India and love your way of teaching because it is always Practical and Concise. I watch your videos on daily basis whenever I Come across any difficulties in understanding concepts of Power BI especially DAX.
Thanks for teaching all these valuable concepts for free of cost.
Thank you, Mr.Ferrari! Great and useful video!
Bravo Maestro! Exactly what I was needing to organize my thoughts about KEEPFILTERS, FILTER and VALUES.
Thank you so much to both of you for continuing to educate us on proper DAX usage. I literally pick out something useful on each of these videos, and am often able to apply it in my next work project.
Enjoyed the video and the article. Clear and concise 👍 Thank you 👍
Excellent, as usual clean and accurate explanation, grazie mille.
Amazing! Very well explained
Thank you very much for this excellent tutorial! I'm already tweaking some measures that I made before knowing this powerful info. You make it SO EASY to learn. :-)
Thank you :), very thought-provoking.
Thanks a lot Alberto, your Tutorials are very valuable.
The question I have is how to use color filters in slicer.
love the thumbnail ♥️
another great video!
This really helped me get over a problem with circular references and keeping multiple filters. Calculate worked great until I added in a DAX table with a column referencing a measure with calculate in it. Not sure if its the best way but I got a solution using the verbose use of FILTER & SUMMARIZE. We will see if I have cracked it 🙂
extremely valuable content
Thank you very much alberto
Hi Alberto, great explanations as always! Thank you.
Could you kindly explain use of KEEPFILTERS in a more complex example used in your book (Definitive Guide), SalesOfFirstYearCustomers (page 385), where you use it in CALCULATE as a modifier and wrap it around table derived from VALUES(Sales[CustomerKey])? You briefly mentioned that purpose of KEEPFILTERS there is to make it possible to filter customers, say by country - but after running so many scenarios, I couldn't find a realistic one where it could really be used to make a difference in Matrix.
Also should it be maybe better to use table SUMMARIZE(Sales, Customer[CustomerKey]) instead of VALUES(Sales[CustomerKey]) in CALCULATE so as to filter by dimension table column?
did microsoft change the behavior of calculate? I didn´t use keepfilters and the matrix shows the results only in red
No - check the sample file you can download.
Great video, Alberto! Please keep doing these common DAX CALCULATEvideos I've been doing DAX for years but love to keep the fundamentals fresh in my mind.
Quick question:
Instead of using SUMMARIZE, can't you just use VALUES twice in the filter arguments of CALCULATE?
Does the following expression work for [Only Red Contoso VALUES] measure instead of using the SUMMARIZE function?
Only Red Contoso VALUES =
CALCULATE (
[Sales Amount],
AND ( 'Product'[Color] = "Red", 'Product'[Brand] = "Contoso" ),
VALUES ( 'Product'[Color] ),
VALUES ( 'Product'[Brand] )
)
It's not the same - in general, you could have arbitrary combinations of color and brand selected, so if the user selected (Green+Contoso) and (Red+Litware) using a hierarchical slicer, the result with VALUES would show Red+Contoso, which wouldn't be included by using the SUMMARIZE version.
Finished watching
What if have a scenario where I'm trying to show blank values as zeros?
Just use "+ 0" in the measure.
Hi, thanks, I think I understand a bit more re SUMMARIZE, am i right in thinking that
SUMMARIZE ie
SUMMARIZE( Table1, Table1[Color]) ,
produces a table in the current filter context, winch is why,
CALCULATE( [TotalSales],FILTER( SUMMARIZE( Table1, Table1[Color]), Table1[Color] = "red"))
does not need Keep Filters, I only found this out while trying it instead of my usual CROSSJON
to create a table of multiple columns to feed to Filter. Oh, Happy New Year. Perhaps I should make resolution, to finally sit and read your book properly.
Using ALL in Filter Function we get same values in table but using values we get value only for red.
I could not get point. Can anyone explain it?
Read this article, and also look for an explanation of the filter context: www.sqlbi.com/articles/using-allexcept-versus-all-and-values/
I'm trying to do the always version but keep getting the value only on the "FirstDate1" any idea why and how can I make it to show this everywhere?
My Code:
FirstValue =
Var FirstDate1 = MINX(ALLSELECTED(Merge1[Dates]),Merge1[Dates])
Var PrvniCena = CALCULATE([mCena weighted], FILTER(ALL('Merge1'[Dates]), Merge1[Dates] = FirstDate1))
RETURN PrvniCena
Hi and thanks for the info. I tried it in ma test file and it's now working properly - formula for "always red" gives me the result as in "only red" (instead of showing all values it shows only one) and to get this result i need to use ALL with FILTER (as in "only red values ext") . Yeah, it's working, but the opposite way. Any ideas why this might happen?
Got the same issue yesterday and was stumped for a while.Finally realised the culprit was the sorting of a column in the visual by another column of the same table.Then I remembered Alberto had posted a video on that some while ago.
Can’t paste the link for some reason now, if you ‘d like to check it out you can search for: “side effects of sort by column”.
Perfect
Sir share dataset
Use the link in the video description.
Can you recommend a power Bi course. UA-cam videos are great but you end up just copying along. Thank you.
See www.sqlbi.com/training
👍❤
Brilliant as always 😀 how many likes can I click?
I have a question:
What if the columns are in different tables. I guess KEEPFILTERS has no problem with that? What about VALUES? Shall we use CROSSJOIN?
It should work without issues.
@@SQLBI thank you so much. but can we use CROSSJOIN of VALUES instead of KEEPFILTERS?
❤❤❤
Then, "only red Values" is no more but "only red Summarize" instead 😉 But I understand the pedagocial purpose !