When To Use The CALCULATABLE Function - DAX Function Techniques

CALCULATETABLE can be a mind-bender when you're just starting out with Power BI. In this video tutorial, I look to cover the main ways you need to think about this particular function and how to use it in Power BI.

Good luck with this one.

****Video Details****
4:17 - calculating new customers using CALCULATETABLE
6:20 - evaluating a customer set
6:51 - adding a FILTER function
8:27 - calculating lost customers using CALCULATETABLE
8:43 - adding the EXCEPT function

***** Learning Power BI? *****

*****Related Links*****

*****Related Course Modules*****

*****Related Forum Posts*****

[scoutsden]

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

[fionamehta]

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.

[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?

[barttitulaerexcelbart]

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.

[sadyaz]

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

[cgzm]

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?

[appolineodhiambo]

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

[rosre]

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] <= Max(Dates[Date]) - [Churn Time Frame Value])? i.e. Max instead of Min? Thanks.

[ianmaddrell]

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.

[whiteface]

Thanks Sam, very interesting and useful!

[sandyproduction]

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

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

[paulbrown]

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

[cesarsaldana]

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

[Silvioamartinezjr]

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

[ChrisSmithFW]

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