Author: Bernat

Hello there!

Today’s post comes from an actual requirement that I had heard before but never handled directly. There is some nifty DAX and modeling so lets get to it!

Continue Reading..

Hello there! Time is crazy and I haven’t been updating the blog as much as I’d like to. But here I am and I hope you enjoy it.

This 2024 I took the decision to step up my involvement at Power BI User Group Barcelona and started organizing online events from international speakers. For those that present in English I even do some live interpretation that is recorded and remastered with the video stream so that we get the renowned presenters in both English and in a not-perfect Spanish translation (check out the events here and the recordings here). But why am I speaking about that? Well, first of all to brag about it because these sessions have been awesome, but also because the most recent one is the starting point for this blog post.

On April 26, 2024, Carlos Barboza raged through 20 charts (yes, twenty) in what was supposed to be a 60 minute session. Well, it turned out to be more like a 90 min session, but it was well worth it. One of the charts was a scatter chart showing the fertility rate and life expectancy in different countries, being able to move the year and seeing how all the dots moved around. He took an article from Robert Mundigl and worked to recreate the interactions achieved in excel. He presented a way of letting the user customize the tooltip, but mentioned that it was not possible to show the path of a single country in Power BI just yet. And this got me thinking.

I haven’t worked much with scatter charts to it took me a while to get the grasp of it. In general we expect to use fields on the axis and measures on the chart area, but with scatter charts is the other way around. The number of data points is the number of different values you have on the field that you use in the «values» well of the visual object, while the position of these data points are indeed the values of two measures, one for the x-axis and one for the y-axis. Of course if one or both measures are blank, the data point will not be shown. All this is very important.  To understand where this is going, let me show you the final result, which could be further polished but is enough to make me happy:

As you can see the final result is quite cool, so how did I do it?

The easy bit

The data model is quite simple. We have a fact table «data» with:

  • Year
  • Country
  • Fertility
  • Life Expectancy

And two measures:

  • Fertility Avg = AVERAGE('data'[Fertility])
  • Life Expectancy Avg = AVERAGE('data'[Life Expectancy])

So far so good.

A number parameter is used to be able to scroll through the years 1950 to 2013. And a relationship is built to filter the fact table.

Also, because we don’t want to use columns from the fact table directly (just in case) we create a dimension for the Country

Countries = all ( data[country] , data[continent] , data[region] )

With this we can create already the standard chart. A data point for each country, and they al move when we slide the year up and down.

 

The tricky bit

Here the first hack.  To highlight the country in a different color and show it’s path, we’ll use a different scatter chart object, placed exactly behind the one just described. Why a different scatter chart? Because we need to show a different granularity of objects, but also (and most importantly) we want to change what is shown based on the data point selected from the first chart. And why behind? Well, otherwise we cannot interact with the data points. With this out of the way, let’s get on into the details.

Our goal is to be able to show for any single country all the values from each different year, highlighting of course the current year. The first important bit here is the granularity of our chart, we don’t want to show just countries, we want to show country-years. At this point is important to note that I’m not claiming that this is the best DAX implementation, it’s just a DAX implementation that works to proof that this kind of chart can be built in Power BI, that’s all. Some of the steps might raise a few eye-brows from a modeling point of view. Bear with me and if you manage to find a better implementation, please let me know. Coming back to the story,  we need to chart country-years,  so we will need a field that has a different value for each country year to put into the «values» well of the visual object. Well, we don’t have that (yet) so we’ll just go ahead and create a calculated column in the fact table, just because I don’t really have the source data ready and I’m playing around with the file from Carlos. And maybe (maybe) to piss off Chris Wagner a little tiny bit.

CountryYear = data[country] & data[year]

Not just happy with a calculated column, I’ll go ahead and create a calculated table as well with all the country-year combinations as well.

CountryYear = 
    ADDCOLUMNS(
        CROSSJOIN(
            ALLNOBLANKROW(  data[country] , data[continent] , data[region] ),
            ALLNOBLANKROW(data[year])
        ),
        "CountryYear",data[country] & data[year]
    )

In this particular case we create a table as large as the fact table, but here the idea is that this table should have the granularity you want for your chart. Inside my head I do think that the chart should be possible without this table, but I haven’t managed to make it work, so we’ll stay the course and show you how I built it. We will now create a relationship between the two CountryYear columns, making sure that the CountryYear table filters the «data» fact table. We are doing crazy things, but not *that* crazy.

So we copy our chart, and replace the Country field by the CountryYear of our new calculated table. We can go ahead and change the color of the markers to a nice reddish color. However, we will not see much difference from our original chart.

What is going on? Well, for starters, we still have the year filtering our data. So even if we have CountryYear as values field, only the selected year is visible. Everything we will do now can be done with just measures, but I like to have simple measures doing the basic calculation, and then solve other stuff (like a filter that should not be there) with calculation groups. This one is quite easy. We want to get rid of the filter from the year slider.

-----------------------------------
-- Calculation Group: 'Remove Year'
-----------------------------------
CALCULATIONGROUP 'Remove Year'[Remove year]

    CALCULATIONITEM "Remove DT years v1" =
        CALCULATE (
            SELECTEDMEASURE (),
            REMOVEFILTERS ( 'Year' )
        )

 

If we apply this calculation item as an object level filter, we get something weird but somewhat closer to what we want. We finally have Country-Years:

Now it’s time to only show whatever is being selected in the front chart. How are we going to do this? Yep, another calc group. This one is a bit sneakier

------------------------------------------------
-- Calculation Group: 'Show Only Cross Selected'
------------------------------------------------
CALCULATIONGROUP 'Show Only Cross Selected'[Show Only Cross Selected]
    Precedence = 1

    CALCULATIONITEM "Show Only Cross Selected" =
        IF (
            ISCROSSFILTERED ( 'Countries'[country] ),
            VAR _selectedCountries =
                VALUES ( 'Countries'[country] )
            VAR _currentCountry =
                SELECTEDVALUE ( 'CountryYear'[country] )
            RETURN
                IF (
                    _currentCountry IN _selectedCountries,
                    SELECTEDMEASURE ()
                )
        )

First it took me a few trial and errors to find the right function to determine if there was any country selected on the top chart. ISCROSSFILTERED is your friend: returns true if one or more data points are being selected. To be honest when I started writing, the dax only considered a single selection, but looking now at the code I realized that it can be made with more than one data point, which is cool. To figure out the DAX we need to get tiny and stay inside a single data point. For that data point, country will only have a value, and it will be the same for all the years of the same country.

Let’s see if it works:

Awesome! But we definitely need to make markers from other years waaaay smaller than the selected year, or the chart is messy and hard to understand. There’s a gotcha here too, so let’s see. My naive daxer just defined a measure to define the size, based on the measure that is used for the front chart. However, that would not work…

MEASURE 'data'[Marker Size Front] = 1

MEASURE 'data'[Marker Size Back] =
    VAR _selectedYear =
        SELECTEDVALUE ( 'Year'[dt years slider] )
    VAR _currentYear =
        SELECTEDVALUE ( 'CountryYear'[year] )
    RETURN
        IF (
            _selectedYear = _currentYear,
            [Marker Size Front],
            [Marker Size Front] / 10
        )

There should be all but one markers with a smaller size, but it just does not work. The problem? THE FILTER CONTEXT!! (what else?) If you rewind a few steps you’ll see we set up a calc group that did what?? Get rid of the filter on year! Ouch. Therefore the «_currentYear» variable is always blank as it sees all the years of the table. So what do we do?? For some stuff we want to get rid of it, for others we don’t. Well, Calc Groups have options to define this sort of things. For this case I went for something quick and dirty like this:

CALCULATIONGROUP 'Remove Year'[Remove year]

    CALCULATIONITEM "Remove DT years v2" =
        IF (
            NOT ISSELECTEDMEASURE ( [Marker Size Back] ),
            CALCULATE (
                SELECTEDMEASURE (),
                REMOVEFILTERS ( 'Year' )
            ),
            SELECTEDMEASURE ()
        )

So the Marker Size Back measure is the only one that can see the year that has been selected. In general I rather define the scope of the calc group rather the «not scope» of it, but in this case I made an exception. Let’s swap the calc item and see the result!

That’s pretty cool I would say!

A reporting Pro like Carlos Barboza or Claudio Trombini would countinue to refine the chart, but I think that for this post this is enough

It’s now your turn to bring it to new heights!

Follow the conversation on Twitter and LinkedIn

 

Hello everyone

With sqlbits, Easter holidays and a bunch of other stuff i haven’t posted in a while, but I’m back!

Full disclosure, this is a sponsored post, but for a good cause, you’ll see in a minute.

If you follow me on Twitter you’ll know I like swag almost as much as Johnny Winter, and man there was a LOT of swag at sqlbits!

And among all the swag, there is a type of swag that every sponsor and plenty of attendees are into: stickers! Stickers are over the place. Many of the fellow attendees had also plenty of carefully curated collection of stickers on their laptops.

I have around 8 stickers so far on mine, very proud of each of them. The latest member is a sticker from Tabular Editor 3! But I have to confess that the one I’m most proud ofis the one of PUG Barcelona 😀

At PUG Barcelona we have a lively whatsapp group –285 members as of today– where the group exists everyday, so it’s not just the events. We share doubts and resources and we congratulate each other on new certifications or even new MVPs of the group (Mar Lizana just became the 6th current MVP of the group! along with Alex Ayala, Diana Aguilera, Iván Arribas, Ricardo Rincón and myself )

Coming back to the stickers… I have shared the super-short run of stickers I ordered at Stickermule with fellow organizers and I want to share similar stickers with those that come to speak at PUG Barcelona and ultimately, ALL members of PUG Barcelona

All these thoughts though go along with another thought… How am I going to pay for them? The super short run is one thing, but like my wallet, it does not scale. So when stickermule offered a nice amount of credit for a blog post with a link I thought it was an offer too good to pass. (Besides the link, the content of the blog post is completely free)

I did three-inch stickers and they look awesome but are a bit big for today’s laptops, so I think I’ll use the credit to create a bunch of two-inch stickers and give them out at PUG BARCELONA events.

If you want to speak at PUG Barcelona (either in-person or online) let me know! We’ll try to make it work, and if we do, you’ll get a sticker! Think about it!

And if you rather create your own stickers, follow this beautiful link!

–>> custom stickers <<–

Be good and take care!

Hello!

The other day on the PUG Barcelona whatsapp group there was a question regarding the possibility of reversing the selection on a slicer, with a single click. Apparently this is something you can do on Qlik and the fellow member was trying to migrate that to Power BI as the users are used to be able to do it. I thought that you can get somewhat close to that with a calculation group, so why not write a nice short article on it. Let’s do it!

Continue Reading..

This is a very recurring topic. You carefully build a dataset semantic model for each business process, and when it all looks good in your Power BI App, some top brass requests a top level summary with key visuals from pretty much all the different models. And they ask for it like for tomorrow. What is to be said? In this post I want to go over the different alternatives, each with pros and cons. Some with fewer pros than cons, but still. At the end I’ll present an alternative when the standard options don’t make the cut. Let’s get started.

Continue Reading..

Hello! Today we leave PowerApps aside, but we head into yet another area way out of my confort zone: Programmatic creation and refresh of custom M Partitions! this is not something I really wanted to do, but I found myself with no other option available. Are you ready? Let’s do it. Continue Reading..

Hello,

The other day I was watching a video from SQLBI and for the first time I had a surprising thought. I thought «Hey, I would not do it like this». In DAX as in other languages, there are some things that are just a matter of style, sometimes is more. Which case is this, is up to you. Today it’s not going to be super-long, I promise.

Continue Reading..

Hello hello, before the topic cools down, let’s complete what we started the other day. As a quick recap we built a Power Automate flow that will launch refreshes of Power BI datasets only when certain ETL jobs of the server have successfully completed. And not only that, those datasets that read from certain high-demand databases will be limited to a certain number of simultaneous refreshes. If you haven’t read it, go and do it. In this article I want to share how I built the PowerApp to manage the ‘Datasets table’ and the ‘Preceeding jobs table’ we discussed on Part 1 of this series. I’m no expert in PowerApps. What I’ll share is what I’ve learned very recently by googling, asking around and tons of trial and error. If there are better ways of doing it, please let me know! with this out of the way, let’s get started!

Continue Reading..

 

Hello hello, it’s been a while since my last post because, well because… stuff, you know.

Anyway, today I want to talk about a solution I put together at one of my customers in order to orchestrate refreshes, so that dataset refresh once the precedent ETL jobs have successfully completed (never before that) and at the same time do not overwhelm the server. I’m sure that there are a thousand other ways to do it, but I have not seen many articles on «Dataset refresh orchestration with Power Automate» so I thought that could help others facing the same struggle. In this first part I’ll focus on the Power Automate side of things and I’ll leave the Power App for the second part. Let’s start:

Continue Reading..

I know, it’s a terrible title, but it’s the best I could come up with. This blog post is to explain a workaround for a behavior I detected while playing with a field parameter (all measures) and a stacked column chart. The problem is as follows: If you have more than one measure in the chart, you can define a color for each measure and all works fine. However, if you filter your field parameter table in a way that only one measure is used in the chart, then that color is ignored and you get a default color. What’s even worse is that the same color will be used whenever a single measure is included, so you can’t even configure it again to get the right color. Fighting the same issue? Keep  reading!

Continue Reading..


Contacto

Contacta con nosotros y descubre el poder de la información.

Nombre
Email
Consulta

Ok! Mensaje enviado.
Error! Verifica los campos.
© Copyright 2022 - Esbrina