When To Use The CALCULATABLE Function - DAX Function Techniques

[@EnterpriseDNA]

*****Related Links***** How To Understand Virtual Tables Inside Iterating Functions In Power BI - blog.enterprisedna.co/how-to-... Deep Dive Into RANKX - DAX Formula Concepts In Power BI - blog.enterprisedna.co/deep-di... Implementing DAX Measure Groups Into Your Power BI Reports - blog.enterprisedna.co/impleme... *****Related Course Modules***** Mastering DAX Calculations - portal.enterprisedna.co/p/mast... Learning Summit Series - portal.enterprisedna.co/p/lea... Advanced Analytics in Power BI - portal.enterprisedna.co/p/adva... *****Related Forum Posts***** Advanced Table Functions: CALCULATETABLE - forum.enterprisedna.co/t/adva... Calculate Table - Subset Based On Unique Column Values - forum.enterprisedna.co/t/calc... Lost Customers Calculation - forum.enterprisedna.co/t/lost... For more calculatetable queries to review see here - forum.enterprisedna.co/search...

[@scoutsden7193]

This is incredibly powerful to be able to change the context of entire tables in one formula.

[@fionamehta7381]

Many thanks Sam, really grateful to you for all your videos and excellent site, explanation on calculatetable() perfect and so easy to understand. - all thanks to you.

[@cesarsaldana3429]

Amazing!!!!Best Regards... From Mexico.

[@EnterpriseDNA]

Hi Cesar, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI and Power Platform video tutorials and announcements. Cheers!

[@gyorgybalogh3194]

Super cool video! Thanks, Sam!

[@SoheilG]

Thank you for this, really helpful!

[@EnterpriseDNA]

Hi Soheil, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials. Cheers!

[@barttitulaerexcelbart9400]

Hello Sam, thank you for sharing knowledge about this interesting function. It looks very much about joins. 2 list of customers: one in the previous period, one in the current period. With Left outer join, innerjoin and right outer join you can make the different customer groups. For me this is a better understanding (CALCULATETABLE is quite difficult at this moment for me). If you agree, you could try once the custom visual of MAQ software: venn diagram. Excellent tool for explaining these concepts.

[@soniyajain7406]

Thank you so much, you saved my day.

[@EnterpriseDNA]

Hi Soniya Jain, glad you appreciated the video. You can subscribe to our channel to see all our upcoming Power BI video tutorials. Here’s the link: kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@whiteface8745]

Thanks Sam, very interesting and useful!

[@EnterpriseDNA]

Thanks, glad to know :)

[@mithunsahaxanderage]

Hi Thank you for such a great insight. I work in training department and our learning modules have 800 learner now they score different marks in different module(course, assessment,etc.) So if I want to find out how many of the learner are above 80% in a specific segment like assessment or course . What fax could be used. I have their marks , total percentage achieved (combined all segments). If you can suggest what dax measure can be used. Thanks. 👍

[@EnterpriseDNA]

Hello Mithun, Thank You for posting your query onto our channel. Well it's always a little bit difficult to judge and provide the results without looking at the data structure, model and working of the PBIX file for the reference. And therefore we encourage you to please write back to us onto our Community Forum by providing the proper description of the problem that you're facing along with the PBIX file for the reference as well as the mock-up results that you're trying to achieve where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference. Hoping you find this useful and helpful. Also make sure that you're subscribed to our KZbin channel so that you don't miss out on any updates pertaining to the Power BI. Happy Learning! Thanks and Warm Regards, Enterprise DNA forum.enterprisedna.co/ kzbin.info/door/y2rBgj4M1tzK-urTZ28zcA

[@sandyproduction03]

HI, I have a table of bookings made over the year, this table has 3 colums (reservation date, creation date, paxes) i made a YOY with the check-in date calculating the amount of paxes and everything is ok, now i want to do the same but filtering the results also with the creation date, for example: i want to know the amount of paxes booked for november 2018 that made the reservations before october 15 218 and the same exact conditions for the last year. How can i solve that? keep doing this type of videos i have learned a lot with it

[@EnterpriseDNA]

Check out this tutorial here, this show you what you need to do - blog.enterprisedna.co/2017/10/16/working-with-multiple-dates-in-power-bi/

[@appolineodhiambo2272]

Hi all reading this comment, assist. i am doing some reports on sales. i have opening inventory, sales and shipments. i want to loop this data such that my closing invetory for january becomes my opening inventory for February and at the same time not affect opening inventory of January

[@ChrisSmithFW]

This is great. Just wish the actual subject started before the ~4-minute mark. Otherwise, good stuff.

[@EnterpriseDNA]

Hi Christ, glad that you appreciated our content! If you haven't yet, you can subscribe to our channel to see all our upcoming Power BI video tutorials and announcements. Cheers!

[@Silvioamartinezjr]

Great stuff but how could you do this with a custom calendar (Fiscal/445)?

[@EnterpriseDNA]

Hi Silvioamartinezjr, thanks for posting your comment here in the video. This topic has been discussed several times in Enterprise DNA Forum. You might get the answers you are looking for. If you want to check it out here is the link: forum.enterprisedna.co/search?q=custom%20calendar

[@sgpleasure]

Hi Sam, in above example if I were to count categories of new customers, in other words, CALCULATETABLE(Values(Customer[Category])), and it is one directional relationship between customer table and sales table , how do I modify your code?

[@cgzm5701]

Thank you for this insightful video! How does CalculateTable differ from Summarize? They both create a set of data and can use table functions (filter, all..etc)and any other function?

[@EnterpriseDNA]

CALCULATETABLE is very different. It works similar to CALCULATE in that you can change the context of the calculation, but in this specific case you can change the context of a virtual table

[@sadyaz64]

Thank you . great tutorial, we need example for how and when using keepfilter.

[@paulbrown1802]

Thank you, Sam, for expanding on the technique. I have a question, (which you probably covered in a previous video on this topic, so apologies in advance). What's the reason you wrap the dates Filter in the ALL() function? (I'll happily view the video in which you explained it if you can head me in the right direction- sometimes it gets difficult to /remember/track the content you include in each tutorial!)

[@EnterpriseDNA]

It's very important in this case because you need to first remove all and any filter from the date table (that's what ALL does) then re-apply them with the logic within the FILTER function. For a general review of ALL see here - kzbin.info/www/bejne/d3rOYqmGgpKoaKs

[@paulbrown1802]

Enterprise DNA Thanks Sam!

[@ianmaddrell2247]

Hi Sam, you seem to have calculated a variable called Churn Time Frame Value, I presume this is calculated on the date table. Possibly calculating 90 days back from today? Great video.

[@rosre075]

Hi Sam, thanks for sharing, you are awesome! I am just trying to work out the logic behind your formula for the 'Lost Customers' measure; I was wondering if there was a typo in the last filter for the variable 'CustomersPurchased'. Should it be: Dates[Date]

[@EnterpriseDNA]

It should be MIN here according to the logic for Lost Customers in this particular example. I only realised the slight error after posting. It is corrected in other example I've done. See here - kzbin.info/www/bejne/oYm9dJ-XbtV9h5Y