DAX Fridays!
HTML-код
- Опубликовано: 5 сен 2024
- In todays video we will go through the differences between all and allexcept dax functions.
Get Northwind Dataset: • Northwind data source:...
Link to DAX Fridays survey: bit.ly/2MMM4KK
Here you can download all the pbix files: curbal.com/don...
SUBSCRIBE to learn more about Power and Excel BI!
/ @curbalen
☼☼☼☼☼☼☼☼☼☼
POWER BI COURSES:
Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:
curbal.com/cou...
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/...
▼▼▼▼▼▼▼▼▼▼
If you feel that any of the videos, downloads, blog posts that I have created have been useful to you and you want to help me keep on going, here you can do a small donation to support my work and keep the channel running:
curbal.com/pro...
Many thanks in advance!Here you can download all the pbix files: curbal.com/don...
▲▲▲▲▲▲▲▲▲▲
Our PLAYLISTS:
- Join our DAX Fridays! Series: goo.gl/FtUWUX
- Power BI dashboards for beginners: goo.gl/9YzyDP
- Power BI Tips & Tricks: goo.gl/H6kUbP
- Power Bi and Google Analytics: goo.gl/ZNsY8l
************
************
QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Linkedin ► goo.gl/3VW6Ky
Twitter ► @curbalen, @ruthpozuelo
Facebook ► goo.gl/bME2sB
#daxfridays #curbal #SUBSCRIBE
You just stopped my hours and hours of madness this morning. Thank you!!! Please continue posting these types of videos. They are so helpful. I rewatch them over and over.
They are like lighthouses in the stormy DAX seas!
I've been taking many tutorials on power bi via youtube from many other people as well. But watching your video, it seems as we are in front of another, and learning from you directly. Above all, you explain things from the very beginner level perspective. Kudoos!
Wonderful to hear!! Thanks for the feedback!! 😀
@@CurbalEN Thanks for making awesome videos. I am stuck in something. Please refer to any of your tutiorial adressing this:
In SUM, things are usually simple but not with Average.
I used ALLEXCEPT to make a measure. Let's just say that 1 of my measures is at City Level and other is at Country Level. The country values are fine as well as Total Value (let's call this World Value = 18.15).
Then I make a calculated table using SUMMARIZE and above measure plainly to generate a table at Country Level. The resulting measure has correct value at the COUNTRY level but not the TOTAL value. That is to say that World Value is a bit different (17.97). This is what we call the average of average problem.
What's the optimum solution to this? I had to recreate the measure in new table referring to columns/measure of old table, that I don't think is an effective solution:
New Table (resulting measure would yield 17.97 as Network value):
NPS_Region = SUMMARIZECOLUMNS( nps[Region], "RegTable_NPSRegion", nps[NPSRegion] )
Measure in New Table referring to columns/measure of old table (this yield correct value 18.15):
Reg_NPSRegional = CALCULATE( 'nps'[NPS], ALLEXCEPT('nps', 'nps'[Region]) )
Original Measure in Old Table (yields 18.15 as Total/Network value):
NPSRegion = CALCULATE( 'nps'[NPS], ALLEXCEPT('nps', 'nps'[Region]) )
That is perfect question for the power bi community. Give it a go!!
I think I am in love with you, Curbal - honestly.
Ruth - tu sei grande! Magnifica! Grazie!
Gracie Linda!
As ever Ruth 2 mins into the video and I understand something i spent an hour on Microsoft documentation trying to get my tiny brain to understand! thanks again!
😊
Thank you very much for the insight into this function. The best explanation so far I have watched on RUclips. Keep up the good work!
The lady in the thumbnail looked all perplexed but that was BEFORE she watched this video. Now she understands. It even now makes sense to me. Thanks, Ruth.
Music to my ears :)
/Ruth
Your explanation si so clear with such great example. I was so confused regarding allexcept and all fucntions. And this video saved my night. Really thank you.
Great video, but be careful, as ALLEXCEPT is removing filter only if used in CALCULATE (because it is CALCULATE modifier). But if you use it in for example in SUMX, it will not remove filters (because ALLEXCEPT in its essence is table function).
Wonderful video! Thanks Ruth!
🥳🥳
Thanks for clarifying this for me.
Great Video Ruth. I was always confused when to use which. Thank you!
My pleasure and happy Friday :)
/Ruth
Ruth is gifted. Makes complex stuff looks easy! But still they are complex :'( Thanks Ruth!
Thanks!
/Ruth
Reminder to myself: watch one of her video everyday!
Wise decision 😉
I had stuggled with allexcept until now... I had seen it as a calculated column in a file which made it confusing because it seemed like a sumif but i didn't know why... thanks, finally I understand!!
Glad to hear!
/Ruth
Another great video, I love your channel Ruth
great video! thanks a lot
Thank you very much, subscribed
Welcome!!
I just wanted to point out: The all except country would have a hilarious effect of combining sales for Paris, Texas/USA, and Paris, France.
Thanks :)
Almost confusing, but interesting at last. Thank you Ruth.
Oh no! Did I confuse you?
:(
/Ruth
@@CurbalEN Anyway, you were able to bring me back on track. I think with practice, it will be clearer.
Thanks Ruth, very well explained
Happy weekend Stephen!
/Ruth
Make sense.. Thanks ❤❤
I love your videos! Thanks for all the content!
Yey!! 🎉🎉 Happy Friday :)
/Ruth
Hello! Thank you so much for this video. I have a question need your help if you will please. How can I show sales of USA only for all rows, regardless of other countries, but still keep other filters from my slicers?
Hi Ruth, my undersatnding is that ALL and ALLEXCEPT *include* all, in direct contradiction to your tutorial. It ignores filters in place. I'm confused.
Thank you, very well explained!
Hi, first thing I would like appreciate for the video, making me to understand ALL VS ALLEXCEPT,
I have a doubt, now I have a column "Gender" from another table "customers", if there is a slicer with gender, when I filter with slicer data is getting updated in the table, so please clarify how to avoid filtering from another table also?
Thanks in advance
we can use All Dax and remove the filter of a column, then what is the use of ALL EXCEPT. Please explain.
How can I use it to apply to provide sales per week using week number column. I tried allexcept week number but it still sums by category
One question
Can we use group by to calculate sales by country?
hi I am using Direct Query mode for almost 1 billion rows , I want to apply filter on a column location here but when i create a measure i need to ignore filter. I have used ALL function like CALCULATE(SUM(Sales[Amount], ALL(Sales[Location])) but it is taking 30-40 seconds. Is there any way i can optimize this DAX?
"All!" does not work with dates?
thx Ruth
You welcome :)
Happy Friday!
/Ruth
Hi Curbal, thank you very much for your videos, this one is very useful, I got the following situation.
I need to iterate 3 columns (Qty * weight * assembly category) where Assembly category has two descriptions: Batter & Packaging and I need to get rid of packaging in the iteration. Packaging is inside the column assembly category. Do you have any video or hint that can possibly help me with the solution. Thank you very much for your time and help.
Hi,
If i have 2 allexept in the calculate formula , does the order matters ? Example :
Allexcept(city ),
Allexept( country ) is different from Allexcept(country ),
Allexept( city)
thank you madam
really a shame that none of these demos ever check against the filters in the filter pane
Well explained video Ruth. Thanks. Do you see the ALL function existing side by side with the REMOVEFILTERS or it will slowly 'disappear'? As I understand, there is no ALLEXCEPT version of REMOVEFILTERS, correct? To be honest REMOVEFILTERS more intuitive to grasp than ALL from a naming concept :-)
All and removefilters do different things, so they are both here to stay :)
/Ruth
@@CurbalEN Thanks Ruth. Something tells me another great Curbal video titled "ALL vs REMOVEFILTERS" is not too far away ;-)
Bare Udusegbe If you watch the removefilters function you will see the difference between all and removefilters :)
/Ruth
Thank you Thank you Thank you!!!!!
🥳🥳🥳
/Ruth
@@CurbalEN Hi Ruth,
One more question on this matter. Do you know why:
[Measure3] = calculate ( [total sales],
Filter( All(Dates),
Dates[Date]
Hi,
I am struggling to make use of ALLEXCEPT when I need to keep filtering of a column that does not come from the same table as the expression / measure (tables have a 1-M relationship):
CALCULATE (
SUM(Table1[ColumnA]) ,
ALLEXCEPT ( Table2, Table2[ColumnB] )
)
However, when I use this formula in a calculated column of the Table2 it works as expected.
Is there a way to make it work as a measure?
good, from now i stop figthing against all and allexcept :D Perhaps a better name could be removeall (filters) and removeallexcept
I just have one doubt in this!
If I want to see only top 5 sales cities, remaining other all will be others like 6th one. What will measure for it.
Thank
You welcome :)
/Ruth
Thanks a lot..
You welcome!
/Ruth
Great video Ruth. Would it be fair to say:
ALL states what we DON'T want to see filtered by/categorized by. I.e., "REMOVE ALL the following filters because we are not interested in them."
ALLEXCEPT states what we DO want to see filtered by/categorized by (like a WHERE clause)? I.e., "REMOVE ALL filters EXCEPT keep the following filters because we are interested in them."
I'm still struggling to find an intuitive way to state all this in English.
Exactly that, well done!
I think of them as:
ALL= REMOVEALLFILTERS
ALLEXCEPT=REMOVEALLFILTERSEXCEPT
/Ruth
@@CurbalEN Thank you!
Funny. Only today I had to do something similar but not the same. Had to create a measure for stock values for different categories of stocks but I needed to exclude just one of the stock categories (but only one of 15 different categories). So ALL or ALLEXCEPT wouldn't work in that situation.. but it's a coincidence that I was working on something similar..
I do so many videos that at some point we have to converge!
.Ruth
Hi Ruth - for ALL I'll sometimes use SUMX(ALL('Table',)'Table'[Metric]). Does using the CALCULATE method have any advantage / different use-cases or is it just two ways to get to the same answer? Thanks!
Use calculate instead of sumx when possible, more efficient.
Check this out:
m.ruclips.net/video/wvbzMwtZSKM/видео.html
/Ruth
Its all about evaluation context, calculate can actually play with evaluation context, and CALCULATE is the base of DAX. any kind of time intelligence function can be derived using this,
In your solution of sumx there may be problem in some cases where the context of calculation is vital.
Hi, i have a question - if you grab data and want to get total for this year e.g. should i mention all the dimension tables to ignore??? Can one removefilters except? Just got that ALLExCEPT is not what you think it is ;)
It is very possible!
Happy Friday !
/Ruth
Hello Ruth,
I am impressed by your videos on youtube, they help a lot. ANd I would like to ask if you know the decision on the following problem. I have a calculated column in my report with total days of ticket progress from submitted day. I need to use conditional formatting depending on ticket type, ticket status of progress and amount of days on each status. For example, ticket type - transfer, ticket status- present letter and if it is less then 7 days the cell must be green, if more than 7 and less than 10 yellow, more than 10 - red and so on. As for now I have created an enormous DAX "IF"measure with all these conditions. And I wanted to know is it possible to use a table with conditions as dynamic parameters. And not to write formula with more than 280 conditions for coloring cells. If it is possible how to do it? So I have a table in excel with ticket type rows and ticket status columns and with deadline days. And if it is less then deadline it should be green, if 2 days before deadline -yellow, and more than deadline -red. I think this case would be very helpful for others too if you make a video. Thanks a lot! I can provide more details if you are interested.
Best regards,
Evgeny
Hi,
To get help , please post in the power Bi community!
/Ruth
Yippie! :)
-kay-yey!! ;)
/Ruth
I didn't understand
They did a poor job in naming "ALL".
Yep, and EARLIER and 🐨 and... 😂😂
DAX is not for the faint hearted!