Thanks a lot, And to anyone reading the comments, yeas you need to add +1. in the end of the formula. sometimes even the best blank out. Thanks Albarto!
Question! scratching my head whole day and I couldn't come up with any good solution. Basically how would you compare two tables? Each are huge and visually impossible to tell if they are the same or one has rows the other doesn't. They have exactly same number of columns and column names.I tried EXCEPTH func but it get error " Function 'EXCEPT' does not support joining a column of type Number with a column of type Text.). Any suggestions?. Again thanks for excellent content!
You need a primary key between the two columns, otherwise it is impossible to relate the two tables. However, you have to write a comparison column by column after you join the tables. I'm not sure DAX is the best choice for these tasks!
@cathater not sure if this is what you are after but: community.powerbi.com/t5/Desktop/Comparing-two-tables-to-identify-discrepancy/m-p/1554190#M635018
Thank you sir for making wonderful videos for us. I am looking for solution from please help me on this, How to find second max date of particular customer in using Dax ?
Is it faster to use datediff here? Also surely you want to add 1 to this equation as every month, Cy2008 and total has the wrong number of days (365 is incorrect as CY2008 is a leap year)
This episode potentially marks a new chapter of sqlbi videos, because Alberto said “have fun with DAX” instead of “enjoy DAX”. This was so unanticipated for all viewers that it has to have some deeper and subtle meaning. 🤔 enjoying something means having pleasure with it in an honest and serious manner, while having fun is much more connected to leisure and free time. Thus, I conclude, DAX is going to be degraded in future from a working horse tool for serious BI applications processing billions of rows in milliseconds to a ludicrous play language for silly people. Bill Gates has planned this on a long term to subjugate us! 😱 And even worse, Marco and Alberto are his secret fellows! I am awake now. 😅
Ciao, so this is why I can't use MAX inside STARTOFMONTH ? Out of interest would you recommend using MAX or LASTDATE as the third argument in DATESBETWEEN, DATESBETWEEN('Calendar'[Date], STARTOFMONTH(LASTDATE(fSales[Date])),MAX(fSales[Date]))) I usually decalare max as a variable. BTW really enjoyed the chapter on variables, never occurred that they weren't affected by calculate once declared . 😊
Thank you. SQLBI Newsletter and TH-cam videos going together. Appreciate it 👍 Are there any links or videos that explains DAX query plans and it's operators in depth ?
@@SQLBI Thank you. Appreciate it and keep up the awesome work. Model optimization would be first I believe before we dive into DAX optimization. (Model which I'm working on has High cardinality columns and its all snapshot fact tables, and every day etl loads millions of records in facts, so looking forward for model optimization techniques. I have learnt so much from SQLBI with regards on DAX optimization techniques 😊 and still awaiting for lots of advanced contents from SQLBI team) Thank you team.
When you need to apply a filter in CALCULATE, LASTDATE is a table function that returns the filter you want. Using LASTDATE in other expressions other than filter arguments of CALCULATE is usually not a good idea.
Really sir the way your explanation with slowness is good.
Thanks a lot, And to anyone reading the comments, yeas you need to add +1. in the end of the formula. sometimes even the best blank out.
Thanks Albarto!
DAX IS THE MOST BEAUTIFUL LANGUAGE FOR ME, AND WITH YOUR HELP IT IS MORE ENJOYABLE
👍👍👍👍👍👍🙂
Unrelated note, you need to add 1 to your formula to get the correct number of days in the period.
You are totally right, my bad!
Fantastic, Alberto!
Question! scratching my head whole day and I couldn't come up with any good solution. Basically how would you compare two tables? Each are huge and visually impossible to tell if they are the same or one has rows the other doesn't. They have exactly same number of columns and column names.I tried EXCEPTH func but it get error " Function 'EXCEPT' does not support joining a column of type Number with a column of type Text.). Any suggestions?. Again thanks for excellent content!
You need a primary key between the two columns, otherwise it is impossible to relate the two tables. However, you have to write a comparison column by column after you join the tables. I'm not sure DAX is the best choice for these tasks!
@cathater not sure if this is what you are after but:
community.powerbi.com/t5/Desktop/Comparing-two-tables-to-identify-discrepancy/m-p/1554190#M635018
Always the basics. Always!
DAX is never as simple as you think..
DAX God! Well explained
So what if the condition is to select Value from separate column for max and min of date?
Could you please help here? Thanks!
Thank you sir for making wonderful videos for us.
I am looking for solution from please help me on this, How to find second max date of particular customer in using Dax ?
Is it faster to use datediff here? Also surely you want to add 1 to this equation as every month, Cy2008 and total has the wrong number of days (365 is incorrect as CY2008 is a leap year)
Great explanation!!!
This episode potentially marks a new chapter of sqlbi videos, because Alberto said “have fun with DAX” instead of “enjoy DAX”. This was so unanticipated for all viewers that it has to have some deeper and subtle meaning. 🤔 enjoying something means having pleasure with it in an honest and serious manner, while having fun is much more connected to leisure and free time. Thus, I conclude, DAX is going to be degraded in future from a working horse tool for serious BI applications processing billions of rows in milliseconds to a ludicrous play language for silly people.
Bill Gates has planned this on a long term to subjugate us! 😱 And even worse, Marco and Alberto are his secret fellows! I am awake now. 😅
You discovered our secret plans!!!
Ciao, so this is why I can't use MAX inside STARTOFMONTH ?
Out of interest would you recommend using MAX or LASTDATE as the third argument in DATESBETWEEN,
DATESBETWEEN('Calendar'[Date], STARTOFMONTH(LASTDATE(fSales[Date])),MAX(fSales[Date])))
I usually decalare max as a variable. BTW really enjoyed the chapter on variables, never occurred that they weren't affected by calculate once declared . 😊
Use MAX, there are no reasons to use LASTDATE that is a table function when you need just a scalar value in DATESBETWEEN arguments.
Very informative
Do you need to add *1* ?
Where?
@@SQLBI to calculate days in month: max(date) - min(date) +1
Yes you need +1 - thanks!
Thank you very much!!!
Thank you. SQLBI Newsletter and TH-cam videos going together. Appreciate it 👍
Are there any links or videos that explains DAX query plans and it's operators in depth ?
Whitepapers:
www.sqlbi.com/whitepapers/understanding-dax-query-plans/
www.sqlbi.com/whitepapers/understanding-distinct-count-in-dax-query-plans/
Video course: www.sqlbi.com/p/optimizing-dax-video-course/
@@SQLBI Thank you. Appreciate it and keep up the awesome work.
Model optimization would be first I believe before we dive into DAX optimization. (Model which I'm working on has High cardinality columns and its all snapshot fact tables, and every day etl loads millions of records in facts, so looking forward for model optimization techniques. I have learnt so much from SQLBI with regards on DAX optimization techniques 😊 and still awaiting for lots of advanced contents from SQLBI team)
Thank you team.
Alberto, ti sei dimenticato di aggiungere +1 in Days in period...
Ops... you are right, thanks!
Good explanation. But what is the purpose of LASTDATE/FIRSTDATE functions then?
When you need to apply a filter in CALCULATE, LASTDATE is a table function that returns the filter you want. Using LASTDATE in other expressions other than filter arguments of CALCULATE is usually not a good idea.
Thanks for the great explanation. One question remains: Why do you need INT()?
The difference between two dates is a floating point number. INT guarantees that the returning type is a whole number.
THX!!
Enjoy DAX!