DAX CALCULATE Trick to Pass Filters from Many to One Side of Relationship

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/many-to-one-file

[@Biin_there]

I have been intuitively developing excel macro codes for the past few years. Now as i rewind and try to refine my methods, your videos have really been very helpful. I appreciate your simple and concise presentations. Thank you.

[@hi_vishy]

Wow! So much information in such a small video. Very crisp and to the point.

[@LeilaGharani]

Glad you enjoyed it!

[@TylehurstXL]

Excellent video, really precise in its content! Ive googles this topic many times and come out more confused than before . All i had to do was use the table as a filter in the calculate function. Neat trick. Googling is over on this topic. Thanks Laila

[@koborkutya7338]

be careful that in larger sales tables using a complete table as a filter may become excessively slow

[@abaderefs]

It's black magic 🧙‍♀🤩your video solved my big issue in data model connections 👍👍👍salute you 👏👏👏👏

[@Noble.Hustler]

You've turned me into a formidable excel guru literary without even studying anything else!

[@alterchannel2501]

I love your explanations, and i absolutely love the way you look up on your left when explaining. 😊

[@juandanielgonzalezchico2459]

I love this video is as well available in Spanish. Muchas Gracias Leila

[@vida1719]

I've never heard that the entire table can be used as a filter modifier. A really interesting approach

[@chh8860]

At the 9:30 minute mark, Leila comments; " ... this was a bit of an advanced dive into the world of DAX ..." A 'bit'??? For me it was a deep dive, ... really deep. Unfortunately, I am still only swimming on the surface with a snorkel ... 😏. But as always, beautifully presented.

[@thinkhelpservice]

yeh i was thinking the same !, but I certainly learnt something new and interesting - just have to get the courage to try it for myself

[@LeilaGharani]

Haha, I understand. Not something you need every day 😊

[@taniarabisheva8381]

Very useful topic for Power Pivots. Thanks Leila Gharani. I learned so much from you.

[@jerrydellasala7643]

Useful, Interesting, and clear explanation! Thank you. 👍👍

[@LeilaGharani]

Glad it was helpful!

[@numba1druma1]

Great! Always coming through with the hard hitters!!

[@wayneedmondson1065]

Great lesson Leila! DAX can be a bit mind bending. You always manage to make it clear. Thanks for the lesson! Thumbs up!!

[@williamarthur4801]

Great video, I have always found many to one side filtering confusing, and still do but thanks for pushing me a bit further along the road to understanding.

[@partymaschine92]

I really love this video! 😊 Sometimes it can be frustrating when filters don‘t work the way as they should! I liked the first way most, because it is so handy and quick. Thanks Leila!!

[@vijayarjunwadkar]

Thank you Leila! Had some idea about this, but your video helped me understand better! You are a great teacher! 😊👍

[@LeilaGharani]

Much appreciated, Vijay!

[@duszan2]

Wonderful lesson - really helps me understand the data flow in data model, and how to make it work in some scenarios.

[@emirtuncer]

Hi, in Crossfilter option you can use Distintcountnoblank function o avoid wrong result in total row. I think using Crossfilter has best performance when compared to other 2 option. Thanks for the video.

[@umangdbz]

I guess it's more to do with ref integrity than blank

[@therealcomment5622]

More than what I was looking for. Thanks!

[@excelwithmark]

Great video thanks for sharing

[@LeilaGharani]

Thanks for watching!

[@bioi0]

Good timing! I was looking for a proper solution for this problem today at work. I solved it with an additional column. But the measure is a much nicer way to do that!😃 thanks for the video, I will change it tomorrow

[@Seftehandle]

May i ask what kind of job requires this knowledge. I work w excel bu cannot see how and where to apply this , certainly i could somehow :)

[@mattschoular8844]

Interesting...Yes, Useful...Yes, Going to need to watch again... Definitely. Thanks Leila

[@LeilaGharani]

Glad you enjoyed it, Matt!

[@GeertDelmulle]

Nice video on the concept of ‘Table Filters’ (where you use an entire table as a filter). Any bridge table will do, here we only have one: the factTable itself. BTW: I do not recommend adding a calculated column to the factTable as an alternative, because factTables usually are very large.

[@LeilaGharani]

Thanks for the feedback, Geert!

[@JuanHernandez-pf6yg]

Very useful. Thank you!

[@lorenzoladejobi8701]

Very useful! Thanks Leila.

[@ahmetyildirim7268]

Very clear, good job. thanks for sharing.

[@pravinr197]

Leila Mam, I studied something called one to one, one to many, many to one and many to many relationships in RDBMS. during my college days. I also read about primary key and foreign key constraints. I recalled those concepts while you were explaining data modelling.

[@LeilaGharani]

👍

[@santoshdevtale]

Brain with beauty. Love u ❤️❤️

[@roycemekolle7334]

Very useful. Thanks for sharing

[@Jocedu06]

Thanks for this nicely contextualized demonstration for the CROSSFILTERS function!!

[@matthiaswellmeyer763]

Thank you very much! Very helpful 👍

[@kishanthacker9999]

I think add a summary slide in the start with bullet list of approaches used.. also by changing to bidirectional we can have same thing

[@giampaolocorradi1783]

Brilliant !!! Thank you. ✌

[@poojandedhia809]

Thanks for a wonderful knowledge sharing.!

[@LeilaGharani]

My pleasure

[@nicopicco]

Excellent, thank you

[@usman_oz]

In 1st solution, I believe it's important to understand the concept of "Expanded Tables" coz that's what I think is responsible for the correct calculation. "Usually" Fact table indirectly contains all the columns of the dimension tables linked to it.

[@Seftehandle]

May i ask what kind of job requires this knowledge. I work w excel bu cannot see how and where to apply this , certainly i could somehow :)

[@partymaschine92]

I am not sure if it is always useful to keep all fields in one fact table. Of course it makes it easier to build a data model. However, if you have to answer different business questions that require different centered data models, translation tables might be more efficient.

[@Seftehandle]

@@partymaschine92 i use xlookup in relation to costs analysis. I cannot see how could i apply this other then building a db in excel

[@partymaschine92]

@@Seftehandle I use DAX and PowerQuery always when I need to repeat certain actions in order to update a workbook. The big advantage of loading external data into the workbook is to keep the actual size of the workbook as small as possible. But I aggree, the usage of DAX is more or less the beginning of a bigger journey which has PowerBI on its path

[@Seftehandle]

@@partymaschine92 last thing, how do you keep the workbook as small as possible.for example if i load 10 excel in one with power query, the data is still loaded in the workbook.

[@patrickbcox]

Very helpful, thanks!

[@juja2819]

Thank you for this one! :)

[@HumpendeTrussetyv3]

Thank you!

[@phupt132]

Thanks for your video, may I check if using crossfilter could lead to the incorrect grand total if somehow a customer has not yet had any sales records.

[@supipisewwandi6804]

Thank you Lia 💐 can you make videos on Minitab

[@roywilson9580]

Of the solutions presentedI think the first would be my goto.

[@ronakshah9561]

Brilliant !!!!!

[@zvikabar-kochva3641]

Thanks. I'm doing dax for a year and a half now, I've heard about calculate trick before, but couldn't undestand it before I saw this video. One thing is missing though. Dax is very flexible language. One may achieve his/hers objectives in so many ways. Efficiency becomes critical when the data set is large, say >1M records. Therefore, I wander which method is more efficient for large data set (suppose memory size is not a problem)?

[@Arjun-Shah-b2r]

Really Usefull

[@chrism9037]

Thanks Leila!

[@anormans8252]

Terimakasih banyak.. thank you so much..

[@DaiSinFa]

Hi Leila, do you know which of the 3 solutions has the best performance? Thanks

[@geoxtream]

Hi thanks for the video. I have a + between two columns - =CALCULATE(COUNT(Pay_23[Full Name]),Pay_23)+CALCULATE(COUNT(Pay_23[Full Name]),Pay_23) what can I add so that on the pivot table when I double click the measure it drills down the right data on another sheet? thank you. I am using a bridge table for a relationship between two tables

[@petr6617]

Measures are something I can't get my head into using. I would also be quite interested in what made you use a separate Calendar table. Thanks, Leila.

[@arifurrahman3883]

Thanks

[@alaaelrafei8907]

Amazing🙂🙂

[@ErikWesseling]

Hi Leila, from a performance point of view, between option 1 or 2, which one would you recommend?

[@baibiarugula544]

I’ve been working on a dashboard and I’m coming across an error. I know it’s gonna be an easy fix but I can’t seem to figure it out. Do you give one on ones ? 🙏🙏

[@elsayedabdalla1640]

Great video thanks, The Grand Total not reflect the correct No, Please clarify

[@ronnyidris]

Hi my macro cannot run in the edge browser, can you give me some tips to make it work, previously I work use IE since IE will be demise, I try to use EDGE..thank you

[@Akn876]

Awesome

[@thinkhelpservice]

like 104 is from me :) thank you Leila another very interesting guide

[@LeilaGharani]

Thank you 🤗

[@juliemcg6935]

New to data models

[@DW_Korell]

In the data model, could the relationship between the customer and sales tables be altered to allow data to flow in both directions, in order to solve the issue? Only I recently saw this same example in a Power BI report and they made the relationship allow data to flow in both directions to do this exact type of report. It was stated by the trainer/demonstrator that this does come with a performance cost, so care should be taken, but I would expect it to be a similar performance cost as using the sales table as a filter in the DAX calculation.

[@LeilaGharani]

In Excel it‘s not possible to have bidirectional relationships. In Power BI it is. But it‘s generally not recommended because it can cause a lot of other issues. It‘s usually best to go via measures.

[@adityadeepakghadge5738]

Please help 🥺 I want to make a script That converts Google sheets data In pdf But problem is i want to arrange data landscape and horizontal way and also want to set margin and set different page set-up A4,A3, legal size .

[@naomifagan2885]

Hi, I’ve recently watched your videos I was wondering if you could point me to the correct videos or post a video explaining some of the below tasks I understand how to create a task list what I’m struggling with is understanding how to create reoccurring tasks that need to be completed monthly etc How to filter todays due tasks in a list to keep on top of them How to move completed tasks to a different worksheet and remove from the main task list Tips for creating a training matrix for employees and it be dynamic Attendance or absence trackers Is there anyway possible say if I have a daily report that I need to fill in and this is a word document to be able to generate a new blank template and save in a particular folder for every new day Creating slicer buttons to access different worksheets or data I’m looking for any tips on an employee dashboard so you could click on employee it would have supervision notes basic information their training information all linked from data in other worksheets How to create a dynamic calendar that you can add events too/ reoccurring events / and changes and updates automatically and I can use continuously

[@msateeshkumar6902]

Hi madam, did you more videos on DAX for power BI

[@arifurrahman3883]

I don't have a computer/ laptop. I want to use Microsoft surface book. It's not available in my country. Now what can I do?? Please help me, if possible.

[@zakishaikhgunners]

Like 176 is from me 🙂 You are Superb!

[@LeilaGharani]

Thank you so much 😀

[@rosesmary]

Will you ever plan to put your Power Pivot course on Udemy? I bought quite a few of your other "power" courses there and love to have them in one place.

[@mohanadmohamed5501]

Grate job, but simply you can create a measure to distinctly count the SalesCity (=DISTINCTCOUNT(Sales[SalesCity]) and you filter it out based on any other dimension linked to the Sales table, and that all you need to do. but if your intention to introduce CROSSFILTER that is another topic you should bring a complex example to best practice its usage

[@mohamedsalah-mm8ex]

Please I need Problem Solved for Date table , I tray create relationship between Date Table to two Table but 1 Relation Active & other relation inactive Please Support

[@jerseyd7131]

If I was to hire someone who could do this, what would I be looking for on a cv to recognise they can do this?

[@LeilaGharani]

Advanced knowledge in DAX and data models maybe.

[@chrismast2790]

I like to imagine Leila making the funny faces at her camera for these thumbnails. It makes me chuckle. I bet she's gotten some odd looks from her husband over the years.

[@jrobinson8898]

Thanks Lia!! Very helpful!! I have an issue where I concatenated 2 columns to create a unique column on 2 tables to create a relationship. However, for some reason when I try connecting I get a many to many error. But when I look at the data in excel there are no duplicates. I don't know what the issue is🤦🏾‍♀️ Let me know if you have any tips. Thanks!!

[@vishalbhati912]

Convert them into text format

[@jrobinson8898]

@@vishalbhati912 Thank you. I will try this. It is currently a number format. Thanks so much!👍🏾

[@vishalbhati912]

@@jrobinson8898 thank me later mam. 1st apply this

[@ricebowl8393]

Good video. But i got lost by the measure with distinctcount 😥

[@LeilaGharani]

No worries. It's not an easy one to follow. If you don't use data models and DAX at work, then you will not need this.

[@rafikfoodway5743]

Vvvvv good

[@badonggarcia1639]

Hi Ms Leila, i've been posting the same query few times, but seems you did not notice me. 🙂 Can you please show us how to copy cells from filtered table and paste it in the same filtered cells but a different file location? Thank you!

[@YoussefTsun]

Genius isnt enough for you

[@shineking4035]

Hello madam,, It is a request from my side .. I am not able to pay and watch your lectures on Excel (macros & VBA) .. Can you please upload the paid videos in KZbin it will be helpful for many students out here ..

[@1yyymmmddd]

Why not just set the relationship to birectional?

[@karimmajerbi8394]

😉

[@truonghoanghaminh4839]

🇻🇳🇮🇳🇬🇧

[@taizoondean689]

Little difficult

[@kanishkatiwary]

From performance point of view- Not a good idea to pass an entire fact table (sales) as a filter....

[@zoranstojanovski8407]

If this data was in microsoft access everything will be simple. Why on earth people use excel to keep data?

[@jjsmallpiece9234]

Wouldn't this be better tackled as an Access database and queries set up

[@alessandrocellini968]

Awsome! ... and not intuitive at all!

[@Wzxxx]

Thank U so much!

[@Wzxxx]

I am curious is there a way to somehow use summarize to accomplish this? I did summarize (sales tbl, months, customer's city) but now i am stuck.

[@mrCetus]

Hi Leila, from a performance point of view, between option 1 or 2, which one would you recommend?