DAX for Power BI Part 5.1 - The Calculate, Filter and CalculateTable Functions

[@darenlim6467]

New version of Power BI is able to use OR across multiple columns. No need to use FILTER anymore. But good learning practise on understanding the use of FILTER function. Thank you.

[@WiseOwlTutorials]

Indeed it is - Power BI and DAX videos become out of date so quickly!

[@nikakalichava8012]

Finally I have time to continue the series. Thanks again, Mr. Owl!

[@WiseOwlTutorials]

Good to see you again Nika!

[@jasonturingan3279]

Thanks sir Andrew for all the tutorials in DAX. You are amazing. You really made it very simple to follow DAX. I hope you cover all the DAX function imbedded inside the CALCULATE function in your future videos.

[@WiseOwlTutorials]

Thanks Jason, happy to hear that you've been enjoying the DAX series. More videos are on the way!

[@samirmishra507]

Thanks Andrew for this. Please make video on other Table Functions like Summarize, Cross join and union and others use

[@WiseOwlTutorials]

Hi Samir! Yes, I will, those topics are on the list of DAX videos to create. Thanks for watching!

[@christophmeirich5928]

@@WiseOwlTutorials Thx in advance, Andrea!!!! Greetings from germany Christoph 👏 👏 👏 👏 👏

[@padynz9869]

Great examples and Excellent Clarity. Thank you very much.

[@WiseOwlTutorials]

You're very welcome, thanks for watching!

[@garrybye4415]

Excellent video. You’ve made it all very simple to follow, thanks

[@WiseOwlTutorials]

Thanks for watching!

[@ItsWithinYou]

Simple yet very effective! Many thanks for sharing this video!

[@WiseOwlTutorials]

Thanks! Happy to hear you found it useful!

[@siddharthrao3115]

I like the accent more than the videos! Just Kidding XD Thank you for the fantastic content man!

[@WiseOwlTutorials]

😄 happy to hear that you enjoyed it, thanks for watching!

[@syedashhar6334]

Thanks for making such an awesome video! It covered all possible combination for Filters with Calculate. This video is a gem🔸🔸

[@WiseOwlTutorials]

Thank you so much for the kind comments and for watching!

[@stickercode]

Mr. Gould, in the section of comparing colums in a filter expression, I managed to handle it without adding "filter" function by this code; // avgruntime of profitable movies = CALCULATE(AllMeasures[Avg runtime], Film[BoxOfficeDollars] > Film[BudgetDollars]) // It let me type this without any error warning and calculated the whole expression. I think it is because of an upgrade by powerBI.

[@WiseOwlTutorials]

Yes, that's correct, DAX is frequently changing!

[@jeetborooah]

@@WiseOwlTutorials I believe that even the CALCULATE TABLE function has probably changed as well as the Avg profits/loss only formula works in calculate table function for me as shown below: Avg Profit Only = AVERAGEX( CALCULATETABLE( Film, not ISBLANK(Film[BoxOfficeDollars]), not ISBLANK(Film[BudgetDollars]), (Film[BoxOfficeDollars] > Film[BudgetDollars]) ), Film[BoxOfficeDollars] - Film[BudgetDollars] ) Thanks for the brilliant video and great work!

[@WiseOwlTutorials]

@@jeetborooah Yes, agreed!

[@mePrafull]

thank you very much for sharing the detailed explaination

[@WiseOwlTutorials]

You're very welcome, thank you for watching!

[@ellukayou]

A complete guide. Love it.

[@WiseOwlTutorials]

Thanks Luciano!

[@mePrafull]

29:21 , in newer version of POwerBI CALCULATETABLE( Film , NOT ISBLANK(Film[BoxOfficeDollars]) , NOT ISBLANK(Film[BudgetDollars]) , Film[BoxOfficeDollars] > Film[BudgetDollars] ) the column references ( Film[BoxOfficeDollars] > Film[BudgetDollars]) are accepted

[@WiseOwlTutorials]

Yes indeed!

[@rajkumarrajan8059]

This is Legendary!!!!

[@WiseOwlTutorials]

Thanks!

[@Coglitics]

Bravo!!!

[@WiseOwlTutorials]

Thanks!

[@mohammedaltaf4877]

excellent

[@WiseOwlTutorials]

Thank you!

[@HaiderAli-vt8wn]

Great stuff, membership icon and link not working , is it available in Pakistan?

[@WiseOwlTutorials]

Hello Haider! KZbin does not currently list Pakistan as an available location for channel memberships support.google.com/youtube/answer/7636690?hl=en-GB#zippy=%2Cavailability

[@yousseflayechi8846]

You can do this with sql or excel but why should we learn this ?

[@TaiTran-ud2jh]

I have a question why don't we use filter ( film, [avg run time] >120) but instead filter ( genre, [avg run time] >120) in 22:08

[@WiseOwlTutorials]

Hi! Because we're interested in returning the genres where the average run time of all the films in the genre is greater than 120 - we need to evaluate the [Avg Run Time] measure for each row of the Genre table. Filtering the Film table would return a table of films where the run time is greater than 120. I hope that helps!

[@PowerBILearner-c6d]

@@WiseOwlTutorials Hi. Thank you for the explanation and the awesome videos. So, does this mean that, unlike in visual filters where filters only propagate from dimension tables to fact tables in a one-to-many relationships, with measures, like in this case, filters can propagate from the fact table (like Film) back to the dimension tables (like Genre) in one-to-many relationships?

[@WiseOwlTutorials]

@@PowerBILearner-c6d Hi! The short answer is no 😀 If you want to modify the direction in which filters propagate in a measure, you can use the CROSSFILTER function (we cover that topic in this video kzbin.info/www/bejne/g5KbaHWkaZKnZ9kfeature=shared) In the example above, the FILTER function is evaluating a measure for each row of the Genre table and checking if the result of the measure is >120. It's not filtering rows of the Film table and then propagating the filter to the Genre table. Hope it helps!

[@PowerBILearner-c6d]

@@WiseOwlTutorials Hi again, Mr. Gould, and thank you for your prompt reply. I don't understand how it is possible to invoke the [Avg Run Time] measure for the Genre. The [Avg Run Time] measure in its internal code contains the field of Film[RunTimeMinutes], which field is not in the Genre table?

[@_huinorman9487]

Can u do the fifo calculations in power bi?

[@WiseOwlTutorials]

Try this community.powerbi.com/t5/Desktop/Using-FIFO-in-calculation/td-p/1124600

[@learnenglishgeo6622]

how to get a member ?

[@RobsondeAOliveira]

Hi! Website certificate expired!

[@WiseOwlTutorials]

Indeed it has, thanks for the heads up Robson, I've informed the people responsible!