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.

Going back in time my first «hit» was this blog post, a C# Script that would build a Time Intelligence Calculation Group using the expressions from Along with the calculation group, the script also creates two measures that work as a dynamic label either as value (text measure) or as format string (the measure value is 0, but the data label would show the dynamic label text).  However, being a measure, the real usage was limited. Normally we want such things in the legend or in the column headers. Today we’ll see a pretty wild workaround to the the fact that as of today, Power BI does not offer dynamic headers. This calculation group and the dynamic label measure are one piece of the puzzle. For those not familiar with it the script creates a calculation group with the following calculation items:

After YOYTD% names get a bit weird, but I think can be understood well. So What about the calculations and dynamic label? It looks like this:

I know, dynamic labels are way to long but that’s the best I could came up with. Please ping me for better labels.

Then there is another important building block that I laid out in this other post, a C# script that given one or more measures and a calculation group it will create the measures for each calculation item, and with the appropriate format string. This last part is trickier than it sounds, since with calculation items you have to evaluate a DAX expression in order to get the format string that will be applied. As a recap it creates an auxiliar calculation group that as a value expresion it has SELECTEDMEASUREFORMATSTRING so this way you can extract the resulting format string of any measure even after the application of one or more calculation groups. The script creates a value expression using a CALCULATE and applying the calc item, and for the format string evaluates the same calculate with the extra calc group on top so the format string is returned and used as the now static format string. This technique will be used again in today’s script.

The third building block is this other blog post. Here I built on the previous blog post and went one step further: After creating all the measures using the calculation group, I created a Field Parameter with two extra columns, one with the measure name and one with the calculation item name. The Field parameter created by the script looks like this:

This provides a better legend that you would have otherwise, but still leaves you wondering for a sec who is CY and who is PY in the current filter context

So I decided to take it one step further and use that measure from the initial blog post to build the name that should be used for the first column of the field parameter. When we create a field parameter, some automatic names are created for us, but nothing stops us from modifying them. However, I wanted to show a different name depending on the year that is selected! How can I achieve that? Well, there is no such thing as dynamic labels in Power BI (at least on January 23, 2023) so the only alternative is … creating a measure for each of the years! Yep, that’s a lot of measures, but the good thing is that the script can take care of that too.

To execute it you need to select one or more measures, or store the script as a macro and choose to show it on measures’ right-click menu.

(Feel free to download the script and follow along)

So basically the script does:

  1. Asks if field parameter should be created (in this use case it’s totally required to do anything useful with the truckload of measures you’ll get)
  2. Checks that there are measures selected
  3. Checks if there is a regular calculation group (the script later creates an auxiliary one)
  4. Checks if the auxiliary group is in place, if not it creates it and requires the user to save changes, recalculate the model and execute the script again (this is required because we will do queries with this calc group to get the correct format strings of the measures)
  5. Asks the user to select the Calculation Group to generate the measures
  6. Asks the user the table where the filter column is (for year that would be the date table)
  7. Asks for the filter column (the year column)
  8. It creates a query to get the distinct values and evaluates it using ExecuteReader, putting all the values in a list ( I don’t know how to do it otherwise). Also you cannot have two ExecuteReader open simultaneously and we’ll need another one.
  9. Then it asks for the dynamic label measure (optional, but necessary for a good effect)
  10. For each measure checks that it has a proper format string and otherwise warns the user and stops execution
  11. Defines a display folder for all the measures related to the base measure
  12. Iterates for each of the calculation items of the calc group selected in step 5
  13. Using the calculation group ordinal column creates an invisible prefix with 0 with white spaces to make sure that all the labels we’ll create are different (I’m not 100% sure this is necessary, but without it, the script would not generate the field parameter).
  14. Iterates for each of the filter values
  15. Defines the measure name as, <base measure name> <calc item name> <filter value>
  16. Then defines the dynamic measure name: if no measure was selected in step 9, it will pick the same of the measure name. Otherwise the dynamic measure name is <base measure name> <dynamic label>, where ‘dynamic label’ is a the result of a query evaluating a CALCULATE statement with the dynamic label measure, the calculation item and the filter column = filter value Something like: CALCULATE([Label as value], ‘Time Intelligence'[Time Intelligence] = «YOY», ‘Date'[Year] = 2020), which will return «2020 vs 2019». The dynamic measure name becomes «Sales Amount 2020 vs 2019» which is the whole point.
  17. The expression for the measure is created with the base measure, the calculation item and the filter column = filter value with a KEEPFILTERS around the last one to prevent people from misusing it a bit.
    Margin YOY 2020 =
        'Time Intelligence'[Time Intelligence] = "YOY",
        KEEPFILTERS( 'Date'[Year] = 2020 )
  18. Now it’s time to evaluate the format string of the measure… or well maybe this could be done outside the iteration of the values — I just realized that. The query would be something like:
    { CALCULATE([Margin],Time Intelligence'[Time Intelligence] = "YOY", 'DELETE AUX CALC GROUP'[Name] = "Get Format String" ) }

    No need to include the filter column this time as the format string will not change (thus could be evaluated before the value iteration loop)

  19. Now with everything in place it finally creates the measure in the display folder, with the expression, format string and a bunch of annotations: One with the same value for all created measures by the script on this run to be able to pick them up later and build the field parameter, one with the calc item order to be able to sort the measures in the same way as the calculation items, then  one for the base measure name, another the calc item name and the filter value. And of course the ‘dynamic’ name too.
  20. Adding them into annotations was the only way I found to pull out this information on the super compact expression that Daniel Otykier used on his script to create the field parameters and that I shamelessly borrowed. Then I customized it to add the extra columns and replace the first column with the dynamic name. Maybe now that all are measures this can be written in a simpler way, but «if ain’t broke don’t touch it» they say, and this statement is quite like sorcery to me anyway, so I touch it the least I can.

And that’s pretty much it! Coding for this script took longer because I took the opportunity to start building the definitive custom class for my scripts. I have now rather sophisticated functions to select a table, select a calculation group, or create a calculation group and calculation item. If I ever need them even more sophisticated I only have to code a little more. I did that with excel a long time ago and it really pays of. I do code a lot less for Power BI than for excel, but we’ll  eventually get there too I guess.

Well wait. With the script we only get the field parameter, a huge one like this (320 rows):

To use this field parameter you might want to build the filter tables for the slicers you saw on the video on top. For that I reused the table of «Time Intelligence Affected Measures» which already contains the names of the measures we are using (most likely). Now for calculation items you can’t just create a relationship with the calculation group, so you might want to create a calc table that is equal to the calc group table. It will contain just the names, but none of the wizardy, so it’s great as a dimension containing all the calc item names. You could enrich that with a nicer names of course. Finally for years you will probably need to a year table. With that you can build like a star schema around your field parameter — how cool is that?

Well but if you just want to use the values included in the field parameter you can probably do without the dimension tables now that I think of it. Anyway, at the end you do get the magic effect of having a dynamic header, but as you see the price (even if it’s just a script) is not cheap.  Make sure you document how you got it working in case someone inherits your model and is asked to add a new measure to the mix. Just delete it and recreate using the same name for the field parameter — it should just work. No warranty on that either though! Use that in production only if you really have to. I hope we’ll eventually get dynamic headers — they could be just a measure like the «dynamic label as value» one and that would save us a lot of trouble. In the meantime we can rewatch the video


Here you can find the script and the pbix

Thank you for reading and please follow the conversation on  Twitter and LinkedIn

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.

Continue Reading..

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.

Continue Reading..

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.

Continue Reading..

Well, well, this is another article about C# Scripts. A topic that deserves much more attention than it’s getting. If you haven’t read my previous post, go do it right now because otherwise writing a c# script is a very frustrating experience. Not as much as building a Data Factory pipeline as a newbie, but almost.

If you have already programmed in other languages, many of this will be obvious, but anyway, is good to see how this applies to C# scripting for Tabular Editor. In this article we’ll talk about checking what is selected, checking what is in the model, interactions with the user and avoiding some of these interactions. Let’s get started!

Continue Reading..

A few months back I wrote an article of what I thought would change the way I (and many more I thought) would write c# scripts from now on. In that article I explored the possiblity of creating a custom dll with all the classes and methods that would make my scripts shorter, more robust and more sophisticated all at once. The idea was cool but not quite convenient nor for development nor for distribution. However something good that came out of that is that my colleague showed me it was possible to have really good intellisense for Tabular Editor C# scripts inside Visual Studio! So even if you are not thinking about custom classes, you should definately be thinking on Visual Studio as your home for actual coding. Today I want to talk only on how to set it up so that it will not let you do anything that Tabular Editor 2 will not accept.

Continue Reading..

Well it’s no secret that I like calc groups. But it’s also true that calc groups have an achiles heel. Calc Item names are constant, so for charts, legends stay the same no matter what. Depending on your use case you might get away with it, but sometimes you might get an end user that will not buy it — and he or she might be an important person, so it’s always good to have a Plan B. Field Parameters have brought fresh air, and indeed are great for such use cases because it’s not like you modify the DAX of the measure in the chart, you actually change the measure, so the name shown in the legend also changes, which is great. However, what happens if you want to create a chart where the user can pick from 4 KPI’s and say 8 time intel calculations?? First you’ll have to create the measures, then the field parameter, then figure out how to model that for the user to be able to select measure and calculation intependently… I mean it’s not impossible, but looks long and boring. Let’s see what we can do about it!

Continue Reading..

Hi, today I want to talk about inactive relationships. Those relationships with the dashed line that unless you invoque them with USERELATIONSHIP they do nothing. The thing is a common pattern if you read this blog — let’s say you have a retail model, and for some charts you need to use the order date and in other charts the delivery date. Other than that measures are the same, such as Sales amount,  total cost, margin etc. Well, you could do a copy of each of the measures and wrap it in a CALCULATE( … USERELATIONSHIP(… ) ) or do a calculation group.  Today we’ll do that, with style.

Continue Reading..

So today I recevied a question on the SML group which looked like the perfect use case for a calculation group. Somebody was trying to build a matrix and wanted to have the values without decimals but the totals with decimals. In other words the goal was defining different formats for values and totals. Without calculation groups, the only way you can get close to that is by using the function FORMAT, but that’s a dirty trick that will come back and get you because now your values are text. If you later use that measure in a SUMX expression for instance, it will not recognize it’s a number anymore. Of course you could keep a measure for the value and a different measure for the formatted value, but hmmm then you would have to do that for all measures that you might want to use this way and well, you would have to manage this sprawling number of measures.  Let’s check both ways in case someone has external tools blocked or something.

Continue Reading..

As soon as I learned how to define transparency in HEX encoding for Power BI, I started thinking on how to control it with a calculation group. After all, you can only define transparency through a color measure, so it made sense. If you have seen any of my most recent presentations on Calculation Groups you might have seen a use case in which I show it’s possible to control the transparency (and light level!) of a color measure with calculation groups. However in such presentations I also say that I’m still looking for a decent use case to put them into practice. Today I’m attempting to do just that with the transparency calculation group.

Continue Reading..

I realize that I use the word «arbitrary» a lot on my blog posts, but then I think, «so what?». Anyway, this blog post is mostly a remake on another blog post, the one called «A truly dynamic tooltip«. When I wrote that blog post, I had struggled a lot to get the effect I wanted, but even though I learned a lot in the process I ended up convinced that it was better to stay out of calculation groups when building such a chart. Well, this is no more. Calculation groups, are ususal, are just fine. You just need to know how to use them.

Continue Reading..

I thought I would let the blog rest for a few days, but I figured out I would write a short one on a topic that Rick brouhgt up on LinkedIn: Controlling Calculation Items with RLS. I lived under the impression that being a table you could just apply RLS like in any other table, but he told me that Power BI throws and error when you try, and sure enough I got the same result. Tried cheating by creating a calculated column in the Calculation group, but the same result. But then I remembered the technique I used on my article on arbitrary two row header tables, and I gave it a try. It does work, so if you’re interested, keep reading. Continue Reading..

I know, I’m back again at validations, but this time is a bit different. Recently I’ve found myselft in a project where the data that was coming in was not fulfilling the sepcified requirements. We tend to thing that data that comes out of a system will always be pristine, but well, at least in one instance it was not. There were some validations set in place in Access + VBA. But then, even if the access was set out to execute every day you have to go in, check the output, and each file had it’s own validation access. Well, not great. Since the patience was running thin (errors kept apprearing in different places) it was decided that a full validatino check was to be set out. Or at least something much deeper than we had now. I figured out that we might be able to build something in Power BI. Data Validation with Power BI. Odd? yes, but hey, you gotta do what you gotta do.

Continue Reading..

Ok, this is one of those things that I might not do in production unless I have the CEO shouting on my ear «I want the bars of the matrix to be smaller so I can read the numbers, and no, you may not use a custom visual for that!». I know that might be quite a narrow use case, but hey, if the CEO wants it, so be it.

Continue Reading..

No calculation groups today. I decided I would document something that I’ve found myself doing quite a lot recently and I guess that other people might experience the same. Migrations. That’s what happens when somebody decides that SAP Business Objects needs to die and be replaced by Power  BI. Or that a the tables generated by processes orchestrated by Windows scheduler runing batch files that open access files with an autoexec macros that in turn execute a mixture of internal operations but some on a SQL Server stored procedures should at least be moved to a proper SSIS package (totally making it up, right?). All these things. At the end of the day you need to be sure that whatever was generated with the old way of doing things, gets created in the new way too. This is something that not much effort is devoted to do, but with some techniques you can have a tool that will speed things a lot. Continue Reading..

Calculation Groups biggest contribution is to help us reduce the number of measures that we have to create and manage. However, sometimes, me might want measures again… Of course we can forgo the calc group altogether, but we might want to keep the centralized logic while having actual measures, and not measures + a filter as we usually do with calculation groups. The most relevant use case that comes to mind is when we do not want our calculation items to travel to the tooltip or drill through pages, as I discussed in this post not long ago. I guess that for most use cases, creating the measures manually is not the end of the world, but hey, scripting is fun and is cool, so let’s automate that a bit.

Continue Reading..

It’s a terrible title, but by the end I hope it makes sense

Today I had some spare time and I thought I would do some c# that is always good to keep your mind going.  The goal was to create a script that given a base measure, you can then specify a column and a new measure will be generated for each diferent value of the column, in a pattern like CALCULATE([Base Measure], tbl[Column] = Value1 ) and the same for value2, 3 etc of that same column.

Continue Reading..

I’m always keep an eye open for potential new members of the Barcelona Power BI User Group. When I see a potential candidate I try to connect and get to know him/her. Today I was surprised that one of these unsuspected new members told me he was already using my script for Time Intelligence (yay!) and followed with a question on calc groups (double yay!). He was trying to build a KPI card which used a measure as the main KPI and wanted to use the previous year value as target, and was struggling to apply the calculation group just to the target one. I gave it some thought and I think the final set up might be useful to more people, so here I come.

Continue Reading..

Hello there, this is not an entirely new technique, but then again, maybe the use case will be useful to someone, so I’ll go ahead and put it here. Indeed the idea came once again from the whatsapp group of Power BI User Group Barcelona, great people all around. Paul shared with the rest of us a solution he brought to a question in the Power BI Community Forum. I that thread (and answering the particular use case) a few solutions involved doing it all in Power Query, while Paul brought in a DAX approach to the solution. Beyond the particular use case of the question, this raises the question: how can we show individual measures in a tabular format? Continue Reading..

Hello there, quite a while ago I wrote a blog post explaining how to override calculation items in tooltip. Basically, if I’m using calc item A and B in a chart, how can I use A B C and D in the tooltip. My solution was to add 2 extra calculation groups with lower precedence, one to remove filters from the calculation group on the main visual, an the other one (copy of the original calculation group) to recreate the desired calculations. This solution does work, but is quite tedious to maintain if you have changes in the original calc group, and (I’m not sure if it’s related) when combined with the time intel dynamic label calc group, it took A LOT to display the tooltip. I tested again a few days ago and even with my new laptop (i7 32GB of ram) it took like 10s, most of them going to «other» things, not the dax query,  so little can be done.

Continue Reading..

Referential what? it sounds weird, but it’s a rather basic thing: It’s like asking, do you have all the product IDs of the sales table in your product table? If you don’t, then is when that infamous «blank» appears in slicers and all kind of bad things start to happen. Unless you load everything from a pristine data warehouse, you should actively check these things, like after each refresh. It’s one of these things that you should do, but normally does not make it anywhere close to the top unless results look way off. If there was an easy way to check that…

Continue Reading..

Hello, hello. After another round of presentations that dried my spare time to blog, just today a new use case came to me through a question in the whatsapp group of the Power BI User Group Barcelona. The goal was to be able to select the measures that should be displayed simultaneously on the same chart. The key here is the plural thing. Otherwise just use the famous script to create a dynamic measure calc group from Johnny Winter and off you go. But here the need is to select the measures from a simple slicer. I want to se measure A, B and D but not C.

Continue Reading..

Hello there, today I don’t have any new fancy use case for calculation groups or anything else, but rather a repository of links to the best of the best c# scripts… to create calculation groups. The main use of this post is to point people this way when I do a presentation on calculation groups, for example. Since they are «one-click» means you can just store it as Macro/Quick Action and even put it in a custom toolbar if you are using Tabular Editor 3 (which you should!). I’ll just start with two, but I plan adding more over time.

Continue Reading..

Ok, so today there are no calculation groups in place, but I want to explain a technique to use incremental refresh even when your source does not support query folding as we normally understand it. Let me explain myself: In general the only use case that is widely documented for incremental refresh is when you load from a relational database that has a static date column (such as creation date of the record). You create your RangeStart and RangeEnd datetime parameters and off you go. And if you only read from your beautiful DWH, lucky you, no need to read further.

In the real world (at least the one that I know) people want to retrieve data from SAP, like a lot. I don’t really understand this SAP thing, but basically they keep scrolling adding columns with weird icons next to it until it’s all in. However, if they try to retrieve too much info, the query fails. So what is to be done?

Continue Reading..

Once again I’ll recreate here a use case I found at work because I think it’s cool and with some gotcha’s that can be fixed.

The use case itself is broad enough. We are measuring the duration of an event (working hours, machine runs, etc) and we want to visualize it. Since we want to compare things, we need to graph a number, however, as humans, we might like to see 4h 30min instead of 270 min. Searching around in google you will find several approaches for transforming a number of minutes or seconds into higher units of time. And that’s a great starting point. For example here’s a great post by Reza Rad. Here we’ll just imagine we have minutes and we want hours and minutes.

Continue Reading..

Is no secret that if you work daily with Power BI, you should be using Tabular Editor,  but if you are working with lots of different datasets you probably feel like you are doing the same thing over and over again. Then it’s time to bit the bullet and get your hands dirty with Tabular Editor scripts. If you do, don’t start with a blank sheet. Always copy from someone and build from there (that’s what I did!) — there are lots of great scripts out there. Maybe not tons, but certainly lots.

But anyway, once you get going with Tabular Editor C# scripts (now we need to specify if we are talking c# or DAX scripts) you may feel that you are repeating code, and as in any kind of programming, that’s not just a waste of time, it’s bad practice. So, today I’ll share how I’m starting to move my scripting to the next level, creating a custom DLL for Tabular Editor C# Scripts (never did that before!) and making use of intellisense by moving development (or at least the bulk of code typing) to Visual studio.

Continue Reading..

I’ve been meaning to write this post for quite a few weeks if not months, and looks like today I may start finally to do it. The detonator has been that I had to do it at work, and Yuki asked about it on twitter. Too much of a coincidence to let it go.

Conditional formatting when calc groups are involved is a bit more challenging, but as usual with calc groups, once you grasp how they work you can do almost anything you want with it.

Continue Reading..

After a long break due to a truckload of presentations (for my standards) here I’m back again with the blog.

I have a pile of ideas, but always the latest idea takes priority as it is hot, it’s burning in my head.

A guy from the Barcelona Power BI User Group asked me if calc groups could help him in a measure he was running. It’s like a measure of measures. There are several measures that represent different organizational KPIs, and they have a disconnected table in which there’s a target for each measure. He wants to know the percentage of targets that were met.

Continue Reading..

It’s a loooong title (well, it was «Conditional Formatting with divergent color gradient for values and totals»), but it’s exactly what I wanted to achieve when I discovered the limitations of the conditional formatting options in the GUI of Power BI Desktop. The key words are «dynamic» in one hand and «values and totals in the other» I know, some of you may think I’ll be doing some copy-cat article of the crazy video from Bas in which he shows how to use the undocumented (that I know of) function hsla(). A function that you need to leave as text?!? Crazy, just crazy. But no, I take a complete different approach and I think it’s worth the effort! Of course you can take ideas from both me and Bas and come up with your own unique approach for dynamic conditional formatting.

Continue Reading..

If you have worked with Matrix visual you probably have faced the problem: Your end user would like to group all the measures that are thrown in into neat little groups, putting all de Quantity- related measures in one group, all the value measures in another, for example. What I’ve seen so far is that people just struggle with the fact that this is not possible in the Matrix visual, and add text boxes on top, as if the header was indeed a two-row header. But it’s not. And if you have a large matrix that requires horizontal scroll, you’ll know what I am talking about. Can we do any better? Yes. Are calculation groups involved? You bet. Difficult DAX? Not at all!

Continue Reading..

Today’s topic is more like an academic exercise than a production use case, but there are a lot of learnings along the way and I think it’s worth the effort.

The other day I saw a tweet by Igor Cotruta that talked about «text fingerprinting». The idea is embedding the user id in invisible characters so that if the exported data get’s eventually leaked it can be traced back  to whoever exported it. Looks like stuff from a spy movie, so I was quickly on-board in the attempt to reproduce that with Power BI with a Calc Group.

Continue Reading..

Ok, I have maybe gone a bit overboard with the title, but I think it has some advantages over other hacks out there to establish the column widths of matrix, so here I am to explain how I came up with it and how to use it.

The first hack I saw was from Ben Ferris (aka The Power BI Guy) which added a dummy measure with a number of 0 to make the width (having automatic width enabled) and then it would disable automatic widths and remove the measure. Nice. But of course, if new columns appear you’ll  need to set the thing again. Something similar happens with the approach of Bas, who skips the dummy measure thing and instead just plays with the format string to show the evenly wide number. His approach is cooler because you skip the measure thing, but you have the same weak points.

However, Bas’s video got me thinking on the topic and the role of format strings…

Continue Reading..

Ok, it’s a long title, but it’s the best I came up with.

So here’s the story. At work there was a sales report with a nice matrix. On the rows, we had several product attributes like category, subcategory and so on. On the columns it was a bit weird because they wanted to show the day of sale, but also compare with the day of sale from the previous year, and with some particular logic for  comparison: if current year had equal or more days of sale than the previous one, it should match day one with day one and so on. However, if previous year had more days of sale, it should match starting from the back, so day one of sale would be matched with day 2 or 3 of previous year. Anyway, this is just to say that I put a measure saying what was the actual day of sale for current year, and previous year, as well as the actual date, because sales are different depending on the day of the week. Then we had cumulative sales, daily sales, for current and previous year. So far so good. Oh I forgot. On top of that there was like % over forecast measure that was the source of all ills.

Continue Reading..

This may seem trivial, but it did not pop up in my head at first, so might be useful to somebody else.

In sales reports, there are lots of numbers. And if it’s a large company these numbers may be very large. So depending on the visual, the full number may be a bit too much, and having just thousands or millions is more than enough.

Continue Reading..

Hi there. In my previous post on how to set up a «data problems» button I did mention that there was a further improvement to the approach, so here I am to explain what is this about.

As you may recall, in my previous installment on the topic, the user is warned that there is some issue with the data through a button that brings him or her to a page where can see exactly what are the issues, such as unmapped items or any other data issues (dates which are not dates, numbers which are not numbers, duplicates…). Today will stick with the mapping problems. In such case you had to copy the offending items, add them to the excel table, and complete the (manually maintained) extended attribute columns.

Wouldn’t it be wonderful if those items could automatically travel to the excel file?

Well, this is exactly what we’ll try to get to in this post. We are going to do data mapping with table connected to the dataset

Continue Reading..

In this post I’ll explain how to break the tyranny of the «all filters» that are passed to the tooltip in particular the filters set by a calculation group which are even nastier to get rid of than regular filters.

It wasn’t intended this way, but this post is sort of a sequel (and not SQL) of my post on dynamic labels for time calculation series, which itself builds on the post introducing the time intelligence calculation group script. If you have not read them you can also watch the video you’ll find on the end of each post — although from the sound quality maybe it’s less painful to read the blog!

Anyway, if you are here probably you know something about calculation groups, and that’s good, because there’s plenty of them coming.

Continue Reading..

I’ve seen it many times. While there is a set of «official» attributes for a product, or a client or whatever, a department may choose to group them through one or more custom attributes. And what do they do? Of course, they create an excel file where they manually maintain a table with the key (hopefully) and all the custom columns. They will tell you that they update it whenever a new value appears in the datasource, but if you double check this statement you might get let’s say «mixed results» at best.

Nothing says «there is a problem» as a red button appearing in your report. And if it offers «actionable insight» on how to solve the problem, so much better. So this is what we’re going to do: A data problem button.

Continue Reading..

As I continue my journey with calculation groups I realize that fewer and fewer people may be interested in what I write, but then I think, «so what?» So here I am.

My script to create a Time Intelligence Calculation Group based on definitions got a lot of attention (for my standards, anyway), so I kept on thinking if there’s anything that would go nice with it.

Indeed, in the latest iteration of the script, there are two measures that aim at providing some sort of dynamic labeling. «Label as Value Measure» is a measure that when in the scope of a Time Calculation of the calculation group, will show the appropriate label taking into account the filters on the date table. For instance, if you select YTD calculation item and year 2008, the measure will show «2008 YTD». And so on. (Label as Format String does the same but using the format string expression so it can be used as sort of dynamic axis labels if you want to place several calculations as the X-axis, but we’ll focus on «Label as Value Measure» which has more general applications)

Continue Reading..

Sometimes we face models which can’t be built because we fall into the circular relationship (which Power BI protects us against) or ambiguity (which sits there silently making all our results meaningless). I faced one of this situations the other day at work and found a workaround with –you guessed it– a calculation group.

Continue Reading..

This is one problem that you don’t realize it’s a problem until you face it.

Happened to me designing a P&L report. For this report, an arbitrary list of items (arbitrary to me of course) has to be displayed in certain order, each with it’s own arbitrary calculation, and even with some hierarchy indentation. Easier said than done, really.

Continue Reading..

This post is based in a true use case. The customer followed the market share trends, to see how it compared to the rest, as a manufacturer, and at a brand level.

So far so good. But coming from excel they were used to build charts any way they saw fit, so they liked to see on the same chart their own year-on-year growth in general as a manufacturer, then in different distribution channels, then specifically the growth of their two main brands, and then the growth on different regions. That alone was my first challenge. But then they said that that they would like to know how others were doing on the tooltip. But of course, when looking at a manufacturer-level value, they would like to see their value together with that for other manufacturers, and when looking at a brand level then wanted to see it with all  the other brands. I added to the mix that it would be nice to see highlighted their own value in the tooltip of course.

Continue Reading..

Ok, by now you probably know I’m a liiiiitle too much into calculation groups. Once you try calculation groups there’s no going back. Particularly if you do time intelligence analysis (that is comparing values with the previous year, but many other things as well). The reason is that normally you would create a new measure for each pair of calculation – measure, (e.g. Sales Amout PY, Sales Amount YTD,  Total Cost PY, Total Cost YTD … ). With calculation groups you just create the box that shifts a measure into producing the time calculation that you want.

Continue Reading..

Ok, this is a rather niche use case, but I’ll go ahead anyway, basically because I think it’s cool.

In conditional formatting in Power BI you have three options: By values, by rules and by measure.  In this last case, you have to provide a measure which provides a hexadecimal code, such as #FFFFFF for white and so on. When I saw this option I liked it because you can write the most twisted logic of the world and format according to that, but I felt uneasy about the fact that you have to hardcode the hexadecimal codes in the DAX logic. WHAT IF YOU DECIDE TO CHANGE THE THEME OF YOUR REPORT??

Continue Reading..

Calculated tables are not used often, because after all, it only combines data that you already have, right? Well, I didn’t use them often, until recently.

I was shown an excel chart displaying market share among top contenders, but including CY vs PY, then CYTD vs PYTD, then MAT vs MAT-1, and then the last 12 months as individual points. «This is what we would like to have»
Continue Reading..