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.
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
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.
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.
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.
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.
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.
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 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
@@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.
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)?
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
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 ? 🙏🙏
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.
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.
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 .
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 TH-cam it will be helpful for many students out here ..
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
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.
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.
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!!
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.
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!
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
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!!
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.
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
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
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
Grab the file I used in the video from here 👉 pages.xelplus.com/many-to-one-file
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.
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
be careful that in larger sales tables using a complete table as a filter may become excessively slow
Useful, Interesting, and clear explanation! Thank you. 👍👍
Glad it was helpful!
I love your explanations, and i absolutely love the way you look up on your left when explaining. 😊
You've turned me into a formidable excel guru literary without even studying anything else!
I love this video is as well available in Spanish. Muchas Gracias Leila
Great! Always coming through with the hard hitters!!
Great lesson Leila! DAX can be a bit mind bending. You always manage to make it clear. Thanks for the lesson! Thumbs up!!
It's black magic 🧙♀🤩your video solved my big issue in data model connections 👍👍👍salute you 👏👏👏👏
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.
Wonderful lesson - really helps me understand the data flow in data model, and how to make it work in some scenarios.
More than what I was looking for. Thanks!
Great video thanks for sharing
Thanks for watching!
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.
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
Haha, I understand. Not something you need every day 😊
Interesting...Yes, Useful...Yes, Going to need to watch again... Definitely. Thanks Leila
Glad you enjoyed it, Matt!
Thank you!
Very useful! Thanks Leila.
Very clear, good job. thanks for sharing.
Brilliant !!!!!
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
Thank you very much! Very helpful 👍
Thanks for this nicely contextualized demonstration for the CROSSFILTERS function!!
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.
👍
Thank you for this one! :)
Thanks for a wonderful knowledge sharing.!
My pleasure
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.
I guess it's more to do with ref integrity than blank
Thanks
New to data models
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.
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 :)
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.
@@partymaschine92 i use xlookup in relation to costs analysis. I cannot see how could i apply this other then building a db in excel
@@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
@@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.
Thanks Leila!
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)?
Terimakasih banyak.. thank you so much..
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.
Hi Leila, from a performance point of view, between option 1 or 2, which one would you recommend?
Amazing🙂🙂
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
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 ? 🙏🙏
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.
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.
Hi madam, did you more videos on DAX for power BI
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 .
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?
Advanced knowledge in DAX and data models maybe.
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 TH-cam it will be helpful for many students out here ..
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
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.
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.
Like 176 is from me 🙂
You are Superb!
Thank you so much 😀
Good video. But i got lost by the measure with distinctcount 😥
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.
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!!
Convert them into text format
@@vishalbhati912 Thank you. I will try this. It is currently a number format. Thanks so much!👍🏾
@@jrobinson8898 thank me later mam. 1st apply this
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.
Why not just set the relationship to birectional?
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!
😉
From performance point of view- Not a good idea to pass an entire fact table (sales) as a filter....
Awsome! ... and not intuitive at all!
Wouldn't this be better tackled as an Access database and queries set up
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.
Hi Leila, from a performance point of view, between option 1 or 2, which one would you recommend?
I've never heard that the entire table can be used as a filter modifier. A really interesting approach
Wow! So much information in such a small video. Very crisp and to the point.
Glad you enjoyed it!
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
Hi Leila, do you know which of the 3 solutions has the best performance? Thanks
If this data was in microsoft access everything will be simple. Why on earth people use excel to keep data?
Thank you Leila! Had some idea about this, but your video helped me understand better! You are a great teacher! 😊👍
Much appreciated, Vijay!
Brain with beauty. Love u ❤️❤️
Genius isnt enough for you
🇻🇳🇮🇳🇬🇧
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!!
Really Usefull
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.
Thanks for the feedback, Geert!
Awesome
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
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
Very useful topic for Power Pivots. Thanks Leila Gharani. I learned so much from you.
Of the solutions presentedI think the first would be my goto.
Great video thanks, The Grand Total not reflect the correct No, Please clarify
Thank you Lia 💐 can you make videos on Minitab
Very useful. Thanks for sharing
Brilliant !!! Thank you. ✌
Very helpful, thanks!
Excellent, thank you
like 104 is from me :) thank you Leila another very interesting guide
Thank you 🤗
Little difficult
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
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 :)
Vvvvv good
Thank U so much!