Hi! actually I was writing another blog post, but today I was shown something that can be done with calc groups that I wasn’t aware of. And not only that, even though the blog post I was shown had some very clever thinking behind, I found there’s still some room for improvement to bring the technique to the next level. We are talking about sorting a matrix by a calculation item placed at the columns section. Let it be said that in many use cases you might be better of just generating the measures with a script (as I showed in this blog post) and using the measures instead. That will give you more control with the UI since each column is indeed a different measure. Yet, there’s something cool in doing things in different way, so I’ll go ahead nevertheless.
This blog post is 90% SQL server and 10% Power BI, so if you are not into SQL Server (or relational databases in general) this might not apply to you. That being said, this blog post explains how to record the size of all tables in the server, and how to measure which tables are being used without enabling query-logging which can bring your server to a halt (or at least so I’ve been told by the DBA).
Some context, in one of my clients they do have most of the data that they manage in a SQL server with ODS replications of operational databases and a data warehouse where all serious reporting should be based. In that sense they have things pretty much sorted out if we compare them with companies that still do critical reporting based on excel files on a SharePoint. Yet, even having a very expensive server we often run into problems and space is always a problem. So we figured to do something about it.
Hello hello, today is C# again. As you know I do like C# scripts for Tabular Editor, as they allow us to automate stuff around Editor, which in turn allow us to do things faster than in Power BI Desktop, so it’s like a super power on top of a super power. In my previous post on the topic, I talked about using a custom class to be able to reuse code, making your scripts, shorter to write, and more robust, as you can put all the bells and whistles once in you custom class and reuse forever. That approach is awesome, but it had still two remaining aspects preventing larger adoption. One is that the set up as a bit of a pain. And the other is that copying the code to tabular editor is not as fast as one would like. You need to select the code of the macro, then the custom class, then fix all the external references at the top, etc. Well, today I’ll talk about a repository that will allow you to start coding in almost no time, and a script that will copy any of the macros of your Visual Studio file *along with the custom class code* and even combining all the external references of both the macro and the custom class. How cool is that?!
Yep, today it’s reporting. Lately I’m trying to give some shape to the governance of an organization, and well, reports having a unified look it’s a good thing. At the very least for those supposed to be «Tier 1» or «The reports that really matter to top brass people». As with business logic, I’m all in when it’s about centralizing stuff in one place, and report themes are indeed this: a file that stores the default configuration for pretty much any setting of your visual objects, page and so on. However, Power BI Desktop does not really allow you to setup everything in the theme, and the alternatives were either editing a huge json file or using tools that were not quite up to the task until not long ago. This has changed and I’m completely addicted to the theme generator that Mike Carlo has put together in Powerbi.tips. IT’S AWESOME.
Hello hello, I’m not writing that much lately due to spring break and a DAX training I’m teaching, but I think I have just enough time to explain a fun use case I found the other day at work. In Real Estate each asset can be in a bunch of different states, especially if there is litigation, squatters, repairs etc. So in this case they want to compare the latest snapshot with the snapshot of one week ago, and see how many assets they have in each state, but also in which state were they a week ago. Let’s get to it!
Hello there, yes, a third part of the snapshot report and I’m not even sure it’s the last one. The thing is that since the last post there has been some major improvements on the set up that I thought are worth sharing. In my previous post I ended up with a small defeat. There were some combinations of filters that when I drilled through to see all the historic records of those order IDs I would not get any rows. Also my set up included duplicating the fact table which is a big no-no in most use cases and a shameful solution from a modeler perspective. Even though this was the best I had, I decided to present the topic on two events, one was the Data Community Day Austria 2023 and the other was @PowerBIEspanol Virtual Conf 2023 (Fin Tour Power BI Days), just a few days apart. The fact that I had to present the solution to a lot of people kept me thinking and looking for solutions, so with the help of the always reliable Ricardo Rincón I finally found out a working solution just duplicating dimension tables and creating dimension tables for everything (even comments and stuff like that). That was much better but not quite scalable. In real life things are ugly and tables have many columns. So while fighting with the same use case at work, I found a sneakier and much better solution that got rid of all those superfluous dimension tables reducing the need of them to just 2. While preparing the presentations I also worked a bit on the report layer and I’ll also share some techniques I came up with that can be helpful at some point. But enough of all this talking, let’s do what? Let’s get to it.
Well, I was happy with the result, but I was even more happy with the reaction on twitter. The dynamic header effect felt like magical to many people, and well, I have many more followers now, thank you all.
In case you haven’t seen it, here’s the video
So how did I do it? Short answer: Field parameters. A little longer answer: Measures and a calculation group create more measures that are put in field parameter with some customization.
Even longer answer? Let’s do it.
Hello again. You didn’t think there would be a second part, right? well, me neither. But as things turn out, I had to work on another report dealing with snapshots, and this time they wanted something fancier. It took me a while to figure it out, but I like the result so I thought it would be nice to share. In the first part, we just showed what went up and what went down, being able to go back and check any snapshot. However, in many use cases that does not tell the whole story. To explain why something went up or down, you need to show what went in, what went out (sometimes important to tell which way it went) and maybe even if the value changed between snapshots. If we just compare 2 consecutive snapshots is not that hard, but things get trickier we take longer time spans and we want to account for everything that happened in between.
Lately I’ve been building a report, which had not happened for a while, and I enjoyed the process. The final result too is simple yet effective, so I thought I would share the approach here because, yes, there is a calc group in place that does some of the magic.
This approach is valid when you want to follow the status of something, warehouse inventory, accounts pending to be conciliated, parking occupation. In all these situations, you are likely to be more interested in the latest snapshot than the previous ones, but at the same time you might be interested in the trends that lead to the present picture, and maybe even go back to a previous shapshot to have a look, without too many clicks. As you can see we’ll need to be smart about the date filter. For some elements we want the last snapshot only for others all of them, and yet we want we want the charts to interact.
Well, hello again. If you just got started with Tabular Editor scrips and programming in general, maybe today’s topic might be a bit too much, but if you either familiar with other programming or already know your way in c# scripts, then this will certainly become a valuable thing: in-script classes! I know it sounds scary and not something that you need, but if you want write awesome code and make your coding life better in general, they you need to pay attention: In-Script classes for Tabular Editor C# Scripts are a thing.