Thanks, never got around to using TREATAS before, my first thoughts were to USERELATIONSHIP, so nice surprise, I think I'll have to go over it a few times, as with a lot of DAX and M.
I need some help from you I am stuck an formula in excel sheet... I tell you the status of the sheet....A1 to L1 it is 12 month heading.....A2 to L13 it is 12 months data.... Now I want the data of these 12 months in one column and in the same sheet..... A16 is the month heading.... In the cells from A17 to A27 for 12 of month data.... I need 12 months of data show just chang the Heading A16..... Is it possible?
I am not exactly sure what you are asking. But THE best Excel question site for back and forth dialog to get Excel solutions (can post a file too) is: mrexcel.com/board
Hey Mike! Question: You could have made a relationship in Power BI within Budget and two other dimension tables and the outcome would have been the same... right? Instead of using TREATAS it would have just been SUM... right?
You can ask about editing. But I can anticipate and answer here: making videos is about 30% panning up from and 70% editing. For me, the vast majority of work is in the editing. All the zoom and callouts and animation and voice editing and film editing is just a small part. For me, most of the story is developed in editing. I do not film and edit in linear order. I go back and forth through video many times to insert and delete and amend later and earlier parts to make the story consistent. Also, I use Camtasia, which is the worst &^%@#&$@$& program, ever created. A lot of the time spent in editing is spent cursing the CEO and programmers of that company for making the most difficult to use program ever. Camtasia can't handle videos over 10 minutes without great struggle. The process that I make is the same as making art: all elements must help deliver a consistent unified message, if an element does not, rip it out and make it right. Sometimes this means spending days editing, only to discover that it is all or mostly wrong, then I must throw out the junk and make it again... So editing is the most important step, most of the story comes from editing, and the finished product often hides the hard work behind the scenes. If you have other questions, just ask.
@@excelisfun well, I fully understand your frustration and anger. Firstly Iwent for camtasia - and its the worst, then I tried davinci resolved (its free for non-companies like youtubers who are normal ppl, its paid for a companies) and still edditing is painfull, to me its so painfull that it has stopped me from recording more then 5 videos. Ive read and tried some other and... it was so bad Idont even remember it name. So I was wondering if maybe you have some1 who actualy likes / loves video editing for video edditing? and to be honest, i dont even know where to find some1 who would be willing to video edit :(
@@ExcelInstructor I have tried other recorders and editors also. Abode Premier is horrible too. Free Cam is too simple. I just do not understand how the Camtasia programmers can be so &^#^%$#$ing stupid to program the app the way they do. I can track how they have changed the coding in Camtasia for a decade and a half, and they always try to cover up coding errors with even worse coding errors. Like on what %^^%#^% planet do you program incremental highlighting with , and . (comma and period) rather than arrow keys!?!?
Great lesson - AS ALWAYS! For the Power BI samples, the Excel file name isn't correct, but changing the Source to either of the 19-M365ExcelClass*.xlsx files seems to work fine.
Hi Mike, another amazing video, thanks dude. I'm testing the alternative BudgetedSales($)NoTREATAS (INTERSECT) measure, and if I´m not wrong, it seems to compute an approximate value not 100% equals to the BudgetedSales($)TREATAS measure. Is this something expected to occur?
INTERSECT does not work like TREATAS. The arguments are reversed and it works off an AND Logical Test. Did you look at the picture of the INTERSECT version in the pdf notes or the Power BI Desktop Finished file?
When I look at the two versions in the Power BI Desktop file I don't seem to see the approximation that you are seeing. I see the same numbers in every cell in the visual.
Nice video Mike. Fantastic presentation of the three methods. I'd probably use the second. Although I dont really have a job at the moment, so I'd only hypothetically use the second method! Good work.
Thanks, never got around to using TREATAS before, my first thoughts were to USERELATIONSHIP, so nice surprise, I think I'll have to go over it a few times, as with a lot of DAX and M.
You are welcome, William!!!
Excel Magic trick again...sir
Glad you like this!!!
Thanks Master Mike! In fact, THEATAS is like those temporary table used in SQL for temporary analysis purpose. very good stuff.
Exactly: temporarily just sitting inside CALCLAUTE, any time you use that Measure!!! You are welcome for the MECS!!!
Data analysis is fun using excel when power tools are more effective and time saving
Nicely said!!!
Thanks Mike for this EXCELlent video.
You are welcome, Fellow Teacher!!! : )
Great channel to learn excel and its tricks, your effort is great service to human hats off to Mr.Mike.Please share your Email id .
Glad you like the cahnnel!
It's awesome to learn data modeling
Yes, indeed = Data Modeling Is Fun!!!!
Awesome video Mike, especially liked the double negative trick. Thank you very much.
Funny how we have to create some odd tricks to hack and get what we want : )
Love you’re explanation of TREATAS.
TREATAS used to only be in Power BI Data Model, but now it is in Power Pivot and Power BI : ) : )
Hi Mike,
I love this series truly!
Probobly i will (finaly) start using DAX not only learn it :)
Cool! DAX is quite good for big data and tables at specific grains!!! Did you watch MECS #17? That's the BIG DAX video : )
@@excelisfun yes, and also i saved it in my work profile with time stamps so i can easly go and refer to the part ill need.
@@ExcelInstructor Yes!!!!!
Freaking awesome 👊 Love that you addded the TREATAS/INTERSECT
Glad is is TREATAS awesome for you!!! : )
Thanks, Mike, learned something new with TREATAS for creating virtual relationships.
Good stuff, great video! :-)
Always glad to help a master like you, Geert : ) : ) : ) : )
I always learn something new from your video, TREATAS. Thank you Mike :)
You are welcome, my ghostly friend!
Great video! Busy day for you!!
Busy, busy, busy, just like Santa Clause getting all the gifts ready lol
I need some help from you I am stuck an formula in excel sheet... I tell you the status of the sheet....A1 to L1 it is 12 month heading.....A2 to L13 it is 12 months data.... Now I want the data of these 12 months in one column and in the same sheet..... A16 is the month heading.... In the cells from A17 to A27 for 12 of month data.... I need 12 months of data show just chang the Heading A16..... Is it possible?
I am not exactly sure what you are asking. But THE best Excel question site for back and forth dialog to get Excel solutions (can post a file too) is: mrexcel.com/board
@@excelisfun ok thanks i try
Hey Mike!
Question: You could have made a relationship in Power BI within Budget and two other dimension tables and the outcome would have been the same... right? Instead of using TREATAS it would have just been SUM... right?
also, Mike, Can I ask a question about Video editing?
Cos thats why i got stuck with my yt channel...
You can ask about editing. But I can anticipate and answer here: making videos is about 30% panning up from and 70% editing. For me, the vast majority of work is in the editing. All the zoom and callouts and animation and voice editing and film editing is just a small part. For me, most of the story is developed in editing. I do not film and edit in linear order. I go back and forth through video many times to insert and delete and amend later and earlier parts to make the story consistent. Also, I use Camtasia, which is the worst &^%@#&$@$& program, ever created. A lot of the time spent in editing is spent cursing the CEO and programmers of that company for making the most difficult to use program ever. Camtasia can't handle videos over 10 minutes without great struggle. The process that I make is the same as making art: all elements must help deliver a consistent unified message, if an element does not, rip it out and make it right. Sometimes this means spending days editing, only to discover that it is all or mostly wrong, then I must throw out the junk and make it again... So editing is the most important step, most of the story comes from editing, and the finished product often hides the hard work behind the scenes. If you have other questions, just ask.
@@excelisfun well, I fully understand your frustration and anger.
Firstly Iwent for camtasia - and its the worst, then I tried davinci resolved (its free for non-companies like youtubers who are normal ppl, its paid for a companies)
and still edditing is painfull, to me its so painfull that it has stopped me from recording more then 5 videos.
Ive read and tried some other and... it was so bad Idont even remember it name.
So I was wondering if maybe you have some1 who actualy likes / loves video editing for video edditing?
and to be honest, i dont even know where to find some1 who would be willing to video edit :(
@@ExcelInstructor I have tried other recorders and editors also. Abode Premier is horrible too. Free Cam is too simple. I just do not understand how the Camtasia programmers can be so &^#^%$#$ing stupid to program the app the way they do. I can track how they have changed the coding in Camtasia for a decade and a half, and they always try to cover up coding errors with even worse coding errors. Like on what %^^%#^% planet do you program incremental highlighting with , and . (comma and period) rather than arrow keys!?!?
Awesome video!
Glad you like it!!!!!
Another outstanding great video!!✌
Thank you, my friend and Teammate: O Masterful Mathematical Excel Lambda : ) : ) : ) : ) : )
Outstanding Mike!!!!
Thanks, Chris M!!!!!!!
Great lesson - AS ALWAYS!
For the Power BI samples, the Excel file name isn't correct, but changing the Source to either of the 19-M365ExcelClass*.xlsx files seems to work fine.
Glad you like it, Jerry! You should not have to worry about source file in Power Query because I loaded everything to Data Model.
Hi Mike, another amazing video, thanks dude. I'm testing the alternative BudgetedSales($)NoTREATAS (INTERSECT) measure, and if I´m not wrong, it seems to compute an approximate value not 100% equals to the BudgetedSales($)TREATAS measure. Is this something expected to occur?
INTERSECT does not work like TREATAS. The arguments are reversed and it works off an AND Logical Test. Did you look at the picture of the INTERSECT version in the pdf notes or the Power BI Desktop Finished file?
Also, TREATAS is mush faster calculating, so we should always use TRETAS. I just showed the old way that we used to have to do it in Power Pivot.
When I look at the two versions in the Power BI Desktop file I don't seem to see the approximation that you are seeing. I see the same numbers in every cell in the visual.
@@excelisfun PDF notes Mike. I'm going to check it again. Possibly I switched the arguments.
@@gvitullib Yah, that is probably it.
Thank you
You are welcome!!!!
Nice video Mike. Fantastic presentation of the three methods. I'd probably use the second. Although I dont really have a job at the moment, so I'd only hypothetically use the second method! Good work.
Yes, the Relationship method is the easiest way to go. Fun. I hope you will get a job soon, Rico!!!!