DAX Fridays!

Подписаться 134 тыс.

Просмотров 26 тыс.

50% 1

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/donwload-center
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/courses-overview
☼☼☼☼☼☼☼☼☼☼
ABOUT CURBAL:
Website: www.curbal.com
Contact us: www.curbal.com/contact
▼▼▼▼▼▼▼▼▼▼
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/product/sponsor-me
Many thanks in advance!Here you can download all the pbix files: curbal.com/donwload-center
▲▲▲▲▲▲▲▲▲▲
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

Опубликовано:

28 ноя 2019

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 87

@tiffanyharris711 3 года назад

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.

@oliveroshea5765 2 года назад

They are like lighthouses in the stormy DAX seas!

@TheFootballRef Год назад

I think I am in love with you, Curbal - honestly.

@ashwinkarki8112 3 года назад

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.

@andreashermle2716 6 месяцев назад

Thank you very much for the insight into this function. The best explanation so far I have watched on RU-vid. Keep up the good work!

@kamranb1369 4 года назад

Another great video, I love your channel Ruth

@aliramadan7425 4 года назад

Great Video Ruth. I was always confused when to use which. Thank you!

@CurbalEN 4 года назад

My pleasure and happy Friday :) /Ruth

@lindabond9307 Год назад

Ruth - tu sei grande! Magnifica! Grazie!

@CurbalEN Год назад

Gracie Linda!

@bravucod 4 года назад

I love your videos! Thanks for all the content!

@CurbalEN 4 года назад

Yey!! 🎉🎉 Happy Friday :) /Ruth

@hasnainhaider8191 3 года назад

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!

@CurbalEN 3 года назад

Wonderful to hear!! Thanks for the feedback!! 😀

@hasnainhaider8191 3 года назад

@@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]) )

@CurbalEN 3 года назад

That is perfect question for the power bi community. Give it a go!!

@stephenbrincat7124 4 года назад

Thanks Ruth, very well explained

@CurbalEN 4 года назад

Happy weekend Stephen! /Ruth

@fatihduzenli5893 2 года назад

Thanks for clarifying this for me.

@markmcc74 Год назад

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!

@CurbalEN Год назад

😊

@bradj229 2 года назад

Wonderful video! Thanks Ruth!

@CurbalEN 2 года назад

🥳🥳

@JonathanCHANAL 2 года назад

Thank you, very well explained!

@animeshkumar1060 3 года назад

great video! thanks a lot

@adriancondie831 4 года назад

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!!

@CurbalEN 4 года назад

Glad to hear! /Ruth

@chavelooo33 3 года назад

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.

@RobertoStaltari 4 года назад

Ruth is gifted. Makes complex stuff looks easy! But still they are complex :'( Thanks Ruth!

@CurbalEN 4 года назад

Thanks! /Ruth

@ebenbrittz535 3 года назад

Thank you very much, subscribed

@CurbalEN 3 года назад

Welcome!!

@Phoenixspin 4 года назад

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.

@CurbalEN 4 года назад

Music to my ears :) /Ruth

@srdjandrljaca7 Год назад

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

@lenanguyen9156 2 года назад

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?

@ayobamiologun9184 4 года назад

Almost confusing, but interesting at last. Thank you Ruth.

@CurbalEN 4 года назад

Oh no! Did I confuse you? :( /Ruth

@ayobamiologun9184 4 года назад

@@CurbalEN Anyway, you were able to bring me back on track. I think with practice, it will be clearer.

@SunilKumarMutte 3 года назад

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

@skdonsingh 8 месяцев назад

Make sense.. Thanks ❤❤

@ramakrishnaravula4350 4 года назад

thank you madam

@mohitgarg360 3 года назад

One question Can we use group by to calculate sales by country?

@audatexsolera4454 4 года назад

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?

@parisaayazi8886 3 года назад

Reminder to myself: watch one of her video everyday!

@CurbalEN 3 года назад

Wise decision 😉

@mchandrasekhar05 4 года назад

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.

@seenureddy5017 3 года назад

we can use All Dax and remove the filter of a column, then what is the use of ALL EXCEPT. Please explain.

@husspirit 3 года назад

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)

@oliverw9691 4 года назад

Thank you Thank you Thank you!!!!!

@CurbalEN 4 года назад

🥳🥳🥳 /Ruth

@oliverw9691 4 года назад

@@CurbalEN Hi Ruth, One more question on this matter. Do you know why: [Measure3] = calculate ( [total sales], Filter( All(Dates), Dates[Date]

@krdhuri 3 года назад

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?

@christinelowery2415 Год назад

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

@penchalaiahnarakatla3378 4 года назад

Thanks a lot..

@CurbalEN 4 года назад

You welcome! /Ruth

@CJamesEnglish 3 года назад

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.

@CurbalEN 3 года назад

Thanks :)

@ihebchafai2780 4 года назад

thx Ruth

@CurbalEN 4 года назад

You welcome :) Happy Friday! /Ruth

@samberger4922 4 года назад

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!

@CurbalEN 4 года назад

Use calculate instead of sumx when possible, more efficient. Check this out: m.ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-wvbzMwtZSKM.html /Ruth

@pravinupadhyay2046 4 года назад

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.

@jevgenijskaktins1066 4 года назад

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

@CurbalEN 4 года назад

It is very possible! Happy Friday ! /Ruth

@udusegbe 4 года назад

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

@CurbalEN 4 года назад

All and removefilters do different things, so they are both here to stay :) /Ruth

@udusegbe 4 года назад

@@CurbalEN Thanks Ruth. Something tells me another great Curbal video titled "ALL vs REMOVEFILTERS" is not too far away ;-)

@CurbalEN 4 года назад

Bare Udusegbe If you watch the removefilters function you will see the difference between all and removefilters :) /Ruth

@feng3625 4 года назад

Thank

@CurbalEN 4 года назад

You welcome :) /Ruth

@MichelleMason04 2 года назад

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.

@Martl1974 4 года назад

Yippie! :)

@CurbalEN 4 года назад

-kay-yey!! ;) /Ruth

@user-bz3gh4dc2y 4 года назад

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

@CurbalEN 4 года назад

Hi, To get help , please post in the power Bi community! /Ruth

@user-jx3rv2re7n Год назад

"All!" does not work with dates?

@EricaDyson 4 года назад

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..

@CurbalEN 4 года назад

I do so many videos that at some point we have to converge! .Ruth

@brucesheffer 3 года назад

really a shame that none of these demos ever check against the filters in the filter pane

@lukereds9975 4 года назад

good, from now i stop figthing against all and allexcept :D Perhaps a better name could be removeall (filters) and removeallexcept

@sweydert 4 года назад

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.

@CurbalEN 4 года назад

Exactly that, well done! I think of them as: ALL= REMOVEALLFILTERS ALLEXCEPT=REMOVEALLFILTERSEXCEPT /Ruth

@sweydert 4 года назад

@@CurbalEN Thank you!