This channel is about the tool Microsoft Hates Greg's Quick Measures (MSHGQM) but covers many advanced Microsoft Power BI DAX topics including an in-depth look at many different DAX functions and advanced DAX calculations.
Note that the views and opinions expressed on this channel are my own and not affiliated with any company.
Пікірлер
We're pixel scaling now, the agenda never dies
Great perspective, thank you for sharing. I hope that MS does watch this and takes it as an opportunity to push boundaries.
A "little more" for capacity is true only for US unfortunately. If someone runs Capacities in West Europe, base price is increased from 5k to 6.8k... I hope that MVPs job is to continue the hype out there, and in Microsoft they see what you present Greg and they will react finally...
I have heard this from others overseas as well that the cost difference is substantially. Plus the loss of PBRS.
@@MicrosoftHatesGreg Yeah, we have some of them as well. Probably it will bring back the topic of cloud mogration, but still feels a bit forced. And for the Capacity cost, as you probably know, in Azure cost always doffers depending on geography and there is exception here.
You got my curiosity up about this and I was wondering if the FILTERS function could help. I wrote a function [My Filters] that looks like this: MEASURE '!!Key Measures'[My Filters] = VAR __ParmFieldFilters = FILTERS( 'Dynamic Field'[Dynamic Field Fields] ) RETURN TOCSV( SELECTCOLUMNS( __ParmFieldFilters, "@Fields", 'Dynamic Field'[Dynamic Field Fields] ) ) In the hopes that it would retain the order in which the choices were selected. It doesn't. 🤡
Nice thinking! I hadn't tried that.
Microsoft is really going to hate you in the morning. LOL. I think there's merit in your critique. They need to focus on what customers are needing. I'm stuck trying to promote user adoption of Power BI in my org (w/o Premium licensing currently) and it's a full-time challenge. So I can't really consider diving into Fabric at this point. It's a shame because I love MS's analytics offerings.
Gotta keep you eye on the ball in this business or you are lost.
This is the crux of Power BI really isn't it? A self service bi tool. Well for a lot of representation and interactivity it works really well. But then at some point you end with a mish-mash of DAX functions to try and navigate your way through some user requirements. It would be fascinating to be a fly on the wall at the pbi team. How much energy are they putting into backing say quick measures and copilot insights into being the last piece of the puzzle for 'self service'? Meanwhile, I'm back to trying to solve my current problem of using 2 field parameters (for x and y) plus a user selectible number of days offset to delve into correlation with lead/lag. p.s. I think chatgpt needed to add 1 to the total number of permutations to cover 0 selections? Thanks for keeping up your postings. It really does help me temper my feeling of helplessness some days :)
As soon as you stated the use case, I was thinking of patient visits. If visual calculations is working, could that be mimicked? What if you assign each level of the field parameter an identifier and then SUMX only those? Definitely showing my ignorance here.
I tried that using the hidden sort column but the problem is that you don't know whether to get a min or max plus it appears that all added fields are always in context at every level. So couldn't find a solution.
Very interesting and useful, many thanks Greg.
DAX and hierarchies will always remains a problem. It would be interesting what MDX would give as a result.
Nice approach, but as far as I understand it FILTER function is an equivalent of CALCULATETABLE... so it's a little cheat as you said ;-)
Well, FILTER and CALCULATETABLE work very differently.
@@MicrosoftHatesGreg 100% OK on some scenarios, but in your scenario you could use both ?
Well, with CALCULATETABLE the ALLEXCEPT essentially replaces the context. With the FILTER, we have to use ALL and then filter to what we want. Also, CALCULATETABLE does a bunch of stuff to build its final context. It's the same process as CALCULATE (from dax.guide). This is way more complex than what FILTER does which just simply filters to the rows specified. This is what CALCULATETABLE does: CALCULATE evaluation follow these steps: CALCULATE evaluates all the explicit filter arguments in the original evaluation context, each one independently from the others. This includes both the original row contexts (if any) and the original filter context. Once this evaluation is finished, CALCULATE starts building the new filter context. CALCULATE makes a copy of the original filter context to prepare the new filter context. It discards the original row contexts, because the new evaluation context will not contain any row context. CALCULATE performs the context transition. It uses the current value of columns in the original row contexts to provide a filter with a unique value for all the columns currently being iterated in the original row contexts. This filter may or may not contain one individual row. There is no guarantee that the new filter context contains a single row at this point. If there are no row contexts active, this step is skipped. Once all implicit filters created by the context transition are applied to the new filter context, CALCULATE moves on to the next step. CALCULATE evaluates the CALCULATE modifiers used in filter arguments: USERELATIONSHIP, CROSSFILTER, ALL, ALLEXCEPT, ALLSELECTED, and ALLNOBLANKROW. This step happens after step 3. This is very important, because it means that one can remove the effects of the context transition by using ALL as a filter argument. The CALCULATE modifiers are applied after the context transition, so they can alter the effects of the context transition. CALCULATE applies the explicit filter arguments evaluated at 1. to the new filter context generated after step 4. These filter arguments are applied to the new filter context once the context transition has happened so they can overwrite it, after filter removal - their filter is not removed by any ALL* modifier - and after the relationship architecture has been updated. However, the evaluation of filter arguments happens in the original filter context, and it is not affected by any other modifier or filter within the same CALCULATE function. If a filter argument is modified by KEEPFILTERS, the filter is added to the filter context without overwriting existing filters over the same column(s). The filter context generated after point (5) is the new filter context used by CALCULATE in the evaluation of its expression.
@@MicrosoftHatesGreg Thanks for your explanation 🙂
That's awesome, for sure it will be useful. One point though, would be even more super duper awesome if you used DAX Query View to show the results of each variable. I wrapped my head around it but it took a minute :D
Very nice! I have a few use cases for that. Thanks!
Man , just what I’m looking . ❤
Wow! That is beautiful ❤
Thanks!
The PBI space wouldn't be the same without you Greg 😂
I don't know art, but I know what I like...😁
Mítica tabla histórica, jajajajajaja que hace arder los ojos a los puristas de dax
I have a bunch of maintenance jobs around the house to do. But following this video, I am now going to take the pressure off and only commit to finishing them by the time Microsoft fixes measure totals.
Lol!
Awesome points, no nonsense approach thanks Greg. Nb, dont you hate the gifs on the forum, I sure do?
I have been on the forums for a while now and seen you a lot, helping people. Didn't know you had this youtube channel. I've subbed now and would like to say thank you for all the time you've spent helping this community.
Thanks so much! Love hearing this kind of feedback! :)
Never have I imagined it is possible to unpivot with dax. PATHITEM()?!😅 I am still dealing with Calculate()
Oh cool! Looks a lot like "Remember 'Storytelling with Data'? Here we're going to apply all that to PowerBI."
If you were to contrast "Practical Charts" with "Storytelling with Data," "Practical Charts" goes into more depth in terms of specific design guidelines (choosing a chart type, formatting scales, dealing with outliers, etc.), and "Storytelling with Data" focuses more on higher-level storytelling skills.
Great stuff! Thanks for the recommendation!
You bet! This one is an easy recommendation!
Delighted that you're getting value from "Practical Charts," Greg, and loved this video! So helpful to show people how to implement good practices in PBI (despite its frustrating limitations).
My pleasure! Thanks so much for making this subject area approachable. I've struggled with it over the years but now I feel like there is hope for me after all!
Awesome. I will be buying that book. Thanks for showing it to us.
It would be interesting to see one of these but comparing the Incremental Refresh Performance in the PBI Service (With vs Without)
Having started with Power BI at the very beginning, the rapid cadence and quality of updates impressed me. I have never experienced that in Office, VB / VBA or various .NET advances or FUBARs. While there are several advances stuck in preview mode, the product does what my clients really need. I share your annoyance about the huge backup of Preview --> GA, as well as cautious paranoia over Fabric's Resistance is Futile Assimilation, but I also find great value in Dataflow Gen2 which allows me to remove nagging dependencies on in-house IT support for ETL. As Gerald Weinberg astutely noted in his opus General Systems Theory, "....all successful systems attract parasites..." It sure looks like the larger entities at Microsoft find value in absorbing and potentially milking a winner. Sad. But we have a winner at hand. Not nothing.
For sure, MS wants everyone in the cloud! Plethora of unused functionalities by customers! But more money. Power Bi Report Server receives even poorer attention. 😢
It is heresy to refuse to drink the Star Schema Kool Aid. And yet, as Greg points out, most Power BI reporting runs will relatively small datasets, which is obviously true: Simple Pareto Analysis. In my experience, any dimensions under five million rows work very well when partially denormalized. Of course, maybe I am just a very, very good architect. And the Schema Fanatics? Often full of hot air (the optimistic substance). It is easy to forget Classic YAGNI - You Ain't Gonna Need It, at least for the first few iterations of any app or reporting requirement. And that's about as far as 99% of them go. Complexity is often High Tech Onanism - yeah, a feel good ain't I really clever - love me & my magic tricks, rather than getting to the nut of the matter. Regarding those who are concerned about scaling to "entereprise grade", certainly a concern for a handful of critical apps, note that you only need to scale UP because you are working with local rinky-dink reporting solutions rather than feeding DOWN from well-designed and professionally curated Data Marts.
Few notes: 1. A flat table is slower for example with DISTINCTCOUNT vs a SUMMARIZE with a star schema. 2. The whole reason for a star schema is to have shared dimensions between facts. If you ever have a second fact up popping up you need to redesign everything, costing more times. And thats is very likely, even in simple models. 3. Because its a bit more complex, doesnt mean its worse. Yes a 12 year old without a license can drive a car, doesnt mean its a good idea. 4. Time Intelligence is hell with a flat table. 5. You dont have to use BIDIRECTIONAL relationships. The dimensions visual can be filtered on COUNTROWS of fact table is not empty, and you will get the same result. Also, you dont always want this behaviour anyway. 6. A star schema is easily explained and ignorance should not be an excuse not to do it. 7. If you hide all non measures in a fact, you will show up as a fact, making it easier to discern dimensions from facts. That is not possible with a flat table if you still want to be able to select attributes. While Im a big fan of Keep It Simple Stupid, a star schema is a bare necessity for Power BI developers, new or experienced.
Well, one could quibble with a star schema with multiple fact tables, I would call that multiple stars in a single semantic model. And if that's the entire reason for a star schema and you only have one fact table then I guess we agree that a star schema isn't necessary in most cases. The rest is pretty much missing the entire point of the video, but I'm out at a bar and don't feel like responding point by point. Maybe later.
Back to this, these are all fine IT arguments. But the video was not made from an IT perspective. It was made from a business user perspective. This is why IT people and business people never see eye-to-eye on things. IT people can only think and talk IT. Unfortunately, when it comes to tech, the narrative is always driven by IT people and not business people and so the business person's perspective is generally lost. That's the point I'm making in this video and, of course, because you can only think IT, you totally whiffed on the entire message of the video. Very IT of you.
@@MicrosoftHatesGreg @MicrosoftHatesGreg Responding to my comment in a bar. Cheers! Anyway, these are not IT arguments. There functional comments. Im not even an IT person, I graduated in Business Ecomics. I consult from big to small business and everyone understands a star schema. These users are usually accountants and controllers. Making a Star Schema is not IT, people were already doing in Excel with PowerPivot. The question you pose is, is Star Schema really necessary because of development time and understanding of the model. In a single pivot table, maybe not, but experience has taught me, it never stays at 1 pivot table. Thats not an IT argument, its common sense.
Eh, I just posted an analysis of Power BI feature releases and I don't see that stretching beyond a single table data model for any reason. There are lots of cases where this is true, probably the majority of the cases. Maybe not the majority of enterprise cases, but the majority of normal, everyday, run-of-the-mill, hey I just want to get a quick answer to something. Like with Fabric Killed the Power BI Star. Nothing to be gained with a star schema. As far as "most users are accountants and controllers", that may be who you interact with but that is not the majority of Power BI users, I assure you. Also, back to the original post, I do TI on single table data models all the time. You just don't use CALCULATE or DAX's TI functions, which I tend not to use anyway.
@@MicrosoftHatesGreg seconding the issue with IT perspective - if they did THEIR job better, we wouldn't have to be diddling around with cheesy desktop schemas.
"Straight to Hell" 😅😅
Straight to Hell on board the Deckler Express - The Ultimate Power BI Contrarian Experience
This is a tiny 1 thousand row dataset , not even 1 million let alone thousands. Star schema is far more performant than a single table & it is extremely easy to setup.
Exactly, the vast majority of business user cases are extremely simple. And "extremely easy" is relative. Compared to a single table data model, star schemas are far, far more complex.
Just a side note. You need to add 2-3 seconds per field you add to the table visual. Because, you know, the default 'helpful' behaviour of Power BI is to go ahead and aggregate columns to Count of or Sum of, because........
Yeah, in my original run through I set the order id to not summarize but forgot when recording the video. Doesn't really impact the performance tests at all.
Deckler's Maxim? LOL. Personally, I create a lot of semantic models in a user-led, self-service environment but I am always thinking about how my models could be deployed at Enterprise scale. I solve the slicer issue by putting a COUNTROWS DAX function in my fact table, then adding it as a filter to the slicer visual with filter value > 0. It solves the issue & I use this technique all the time. I won't speak to size & query performance issues since you are quite adept at that & I am still learning query timings. But I will say that a star schema w/ relatively uncluttered fact tables, with dimensions pushed out as 'lookup' tables, makes for a simpler data model from a design perspective and user comprehension standpoint. I definitely concede your point that Power BI newbies can get by with a single table model and, hopefully, later on migrate to more complex model structures.
That's a good work-a-round but even more technically complex than just using bi-directional relationships. I mean, code. And, to be clear and I hope I made it clear in the video, star schemas are fine for advanced users and IT type people, but it's overkill for the vast majority of end user business scenarios. It's like hitting a gnat with a sledge hammer. The point I'm trying to make in this video is that, similar to CALCULATE, the common narrative out there simply doesn't apply to the vast majority of people using this product. It's unfortunate that the, by far, largest persona is almost never considered in discussion about Power BI be that CALCULATE, star schemas, etc.
@@MicrosoftHatesGreg Absolutely. I’m a bit of a purist but it’s really hard when you have a relational data warehouse & no SQL background to develop star schemas. Just get started & pick up more skills along the way. I learned SQL over a few years and it has helped my modeling enormously. Still have some annoying org IT restrictions that complicate things.
I guess my counter is that you learn a lot more thinking at an enterprise level. And a lot of business users should be trying to think at a larger scale because it's good for them :). I also find a proper semantic model easy to understand whereas a flat table is just one big mess. I would like to see more discussion on this. I also thought it was understood that vertipaq engine is so efficient even flat tables get nice performance for simple use cases. But at scale flat tables can falter. If you have evidence contradicting the Sqlbi guys I am sure they would love to see it. I don't think they would disagree about small data size.
This is absolutely about scale. My guess is that 80% or more of semantic models in Power BI don't ever need to be more complex than a single table data model and similarly 80%+ of Power BI users are business users that just want to get do some data analysis and visualization without having to know all the complexity of the product. Also, consider this, up until 4 years ago, the data model in Tableau was essentially a single, flat table. All tables added to the data model ended up as a single, flat table in the physical layer. And that worked just peachy for a long, long time and ushered in a huge wave of data analytics and visualization which Power BI simply rode and struggled to catch up to for years.
@@MicrosoftHatesGreg I meant to ask. I do not know Tableau but I have heard it falters with more complex models because its just a flat table. And may also struggle at scale. Do you have knowledge of this? I am curious how Tableau has done so well since it violates the data warehouse toolkit.
The new data model for Tableau actually introduces a logical semantic model layer that keeps the data normalized instead of de-normalizing it. So it's essentially the equivalent of Power BI's semantic model. There are some complex data model scenarios that are "unsupported", like multiple fact tables related to multiple shared dimension tables and directly relating 3 or more fact tables on shared dimensions but both have workarounds or just caveats to keep in mind. This is the best write-up I have found on the topic: help.tableau.com/current/pro/desktop/en-us/datasource_datamodel.htm
One thing you rarely find is an enterprise-grade Star Schema. Every discussion I see starts with a core Fact Table and 4-5 points on the star called Dimensions. And yet, in any significant enterprise-grade application, more than half of those "dimensions" are actually significant and data-intense Facts. So how does one actually scale a Star Schema? Bus Architecture (Kimball)? Multiple focused Datamarts (Inmon)? Or just common sense (slightly denormalized 3NF databases Referential, Domain and Entity Integrity enforced)? Aside from rapid prototyping or proof of concept experiments, I don't see why scaling UP should ever be an issue. If truly "enterprise" then start with the enterprise and work DOWN to a manageable Semantic Model. It is far easier to start with a solid source of data and denormalize a bit with simple SQL Views than build-up from a lot of Power Query and Dax mumbo-jumbo. (shades of Roche's Maxim for sure)
You’re definitely going to have to invoke the Little League Slaughter Rule when you start ringing up the tallies on TI. By my count, TI under DAx adds 37 New functions, TI under No CALCULATE: 0.
Sometimes I get a "Northern Conservative Baptist Great Lakes Region Council of 1879" vibe from the CALCULATE crowd.
Lol! That's a truly unique reference!
@@MicrosoftHatesGreg Emo Phillips, weird, but a genius.
That's a pretty funny joke. I just looked it up.
You mentioned, with no calculate approach is not needing something about star schema?
@abediaz6707 Well, nobody really needs a star schema and the star schema is all kind of smoke and mirrors to a certain degree. But the point is that you often end up filtering on the fact table with No CALCULATE which isn't as bad as it sounds.
We are still on premium Capacities, and thanks to EA we have time to prepare for a new reality, with more expensive Capacities, storage cost, and no Report Server included, which is... controversial. Still, I try to look at it from perspective of solving problems in Power BI world. Example you've used, to make a single table semantic model transformed into a warehouse, of course doesn't make much sense. But shift from Dataflow Gen 1 to Dataflow Gen 2 + Lakehouse, this is a completely different story. Orchestration with Pipelines also brings a lot of value to handle end-to-end process, without REST APIs, Service Principals and coding. I am still waiting for a good use case to test Real-time analytics, but so far it does look better than the options we had without Fabric. Data activator is an interesting tool as well, especially since we use a lot of Power Automate. Where I am totally blind, especially on how it will impact Capacities, is the Data Science area. I can imagine that model training activities will be quite heavy on resources... Which is already a problem, as far as I know all the new workloads are background activities. Everything related to Data Ingestion is fine, as they are kind of "predictable". The problem starts when we have a lot of purely interactive activities, like querying a warehouse, running ad-hoc analysis with notebooks, even using Copilot (which could be a Capacity killer), is recognized as background activity. So, there are a lot of unknowns that will make taking care of capacities a very difficult task... Anyway, I definitely see a lot of added value and a lot of concerns. Shared my thoughts, and going to sleep :D
Great feedback. And I agree that for existing Premium capacity customers, Fabric adds quite a bit of functionality (well, once some of the limitations are going as I didn't have much success with Dataflow Gen 2 + Lakehouse). But, existing Premium customers aren't really net new in my opinion and that's because there really isn't THAT much new/additional revenue coming in from someone with an F64 versus a P1 for example. So the vast majority of that revenue is already coming in from Premium capacity customers with only a fractional uptick in terms of the additional expense of Fabric. So, I don't see that as much ROI for Microsoft quite honestly, not enough to justify the huge expense of developing Fabric. Really great thoughts on this topic. Greatly appreciate it! This is a fascinating subject to me. And the retiring of Premium SKU's, well, I think I referred to it as "pure evil"...
Orchestration in ADF is much better than data factory in Fabric.
@@notoriousft Absolutely, ADF in Fabric is horrible. Greg should do a video on ADF in Fabric, its hilariously bad.
@@notoriousft No argue here, I hope that gaps will be closed at some point of time. But still, how common is it for Power BI Developers to have access to Azure? Even if Fabric Data Factory is far from being perfect yet, it does bring additional capabilities to a single platform.
Fully with you@@MicrosoftHatesGreg on Revenue part. Premium users are already existing customers. The chance I see though is that more consumers will be attracted to Power BI because the Fabric? I don't know, maybe I just want to see it succeeding. But, as always, thanks for great video Greg. I love how you bring additional context to the discussion.
And now the Optimizing DAX book is out.
Wonder of it covers this kind of issue with CALCULATE and the query engine...
Yup, this deserve a celebration 🍾
Part of my daily routine is wordle, then check in and see if Greg has posted up some more humour that I can reflect on if I have to wrestle with some 'should be easier than this' PBI challenge :)
Greg para los otros videos, agrandar la pantalla de los dax, se ven muy pequeños desde el modo pantalla pc
Will do!
I'd be interested to see report design examples of yours. Your logic is always sound, but how do you incorporate multiple date filters in a page? Things like that.
Not sure I completely understand the question here.
@@MicrosoftHatesGreg I'd be interested to see report *design* examples of yours, specifically.
I was very surprised when the GA announcement was made...
Massive breath of fresh air - not sure how I got here, but I'm here to stay
Awesome! Glad you enjoyed it!
Love it when the tables are turned to highlight hypocrisy!
Oh dayyum, fightin' words there at the end.🤣🤣
Keep it up Greg. More fire and brimstone.
Let me jump right now to forum and have a look at it
100%, the pattern is what makes it easy to understand.