In this episode of Fabric Tech Talk Friday, join Patrick LeBlanc and Ville Gulltrand, a Data Platform MVP from Sweden, as they share four game-changing Power BI tips that can save you time and improve your workflow. From multi-selecting measures for formatting to advanced Power Query tricks, scorecards, and non-linear numeric ranges—these insights will make your Power BI experience smoother and more efficient.
Full Transcript
Introduction: Welcome to FAB Con TV(00:00:00 – 00:00:21)
Patrick Le Blanc
Hello, everyone, and welcome to FAB Con TV. I’m your host, Patrick Le Blanc, and we’re here in beautiful Vienna.
Today, I have Ville with me. Ville, before we get started, tell the people who you are and what you do.
Ville
All right. My name is Ville. I’m a data platform MVP from Sweden, and I do Power BI.
Setting the Topic: Things Ville Wishes He Knew (00:00:21 – 00:01:00)
Patrick Le Blanc:
You do Power BI. All right. Tell us what we’re talking about today.
Ville:
All right. Right now, we’re talking about four things that I wish I had known when I started working with the program.
Patrick Le Blanc:
Wait a minute. That you wish you’d known? When you started, you didn’t know these things.
Ville:
I didn’t know these things.
Patrick Le Blanc:
These things will make your life better?
Ville:
No worse. No better. Much better. In all fairness, the first thing, easiest thing, a little bit embarrassing. I don’t think I knew this maybe for the last two or three years.
Ville:
You can select multiple measures in the model view and format them all at once.
Tip #1 – Multi-Selecting Measures in Model View (00:01:00 – 00:01:55)
Patrick Le Blanc:
Okay, so let’s play a game. Every time you do something, I’m going to say if I didn’t know it right now today.
Ville:
Right, right, right. Okay. Yeah. Okay. All right.
But this one, you know. Number one. Number one. Number one.
First of all, ever seen a model with only measures?
Patrick Le Blanc:
I have not seen a model with only measures.
Ville:
That’s one. One zero to me.
Okay, it’s right. The thing is, we have these measures and the whole numbers, but I want to have them as currency.
Sure, I can go in and select them one by one, but if I go to the model view, I can suddenly hold down the CTRL key and now I can select multiple measures, which, for example, means I can switch the formatting on multiple measures at the same time.
It’s not a big thing.
Patrick Le Blanc:
But it’s much more efficient. I didn’t know this one, though. I did know.
Ville:
I figured it out.
Patrick Le Blanc:
But it’s much more efficient. Think about instead of going one measure, one measure.
If they’re all currency, I just multi-select them, change the format, and they’re all — no waiting for the loading to just say, oh, I’m doing this.
Ville:
It’s all done.
Patrick Le Blanc:
That’s number one, right? That’s number one. One to one. It’s one to one.
Tip #2 – Reusing Power Query M Code Inline (00:01:58 – 00:03:47)
Patrick LeBlanc:
Ville:
One to one.
Okay, so let’s see the next one. This one has to do with Power Query.
I have a table, see it here, beautiful table, except category ID. It’s only the IDs. I obviously want to bring in the actual category.
You can see here on the left side, I have categories. It holds a category ID and the actual category.
What I’ve done, as you can see on the right side here, I have the applied steps. I’ve merged with that table and I’ve expanded the categories, and then I’m going to remove the category ID.
I have a beautiful table at the end. You can see this is looking good.
Problem is now I’m left with a category table, a query that I don’t really use.
Don’t really use.
No, exactly. I want to get rid of that.
I could not load it, sure. But what I’m going to do is I’m going to go in here, I’m going to go to View and then go to the advanced editor.
Give me just a second. I’m talking faster than I’m doing.
I’m going to just copy this code right here, and then I’m going to go back to Tickets and open this advanced editor.
Right before the merge step, I’ll just paste this in.
And watch this. I’ll paste it in, and then I format it a little bit better, and I name this step category table with an equal sign.
I put this in the middle of my M code, and then I finish with a comma, and I just take that little reference and I merge it.
Instead of merging with the category table, I merge it with that part of my M code.
Patrick Le Blanc:
I did not know this.
Ville:
And now I no longer. Two to one. Two to one.
Patrick Le Blanc:
You win. I did not know this.
Ville:
That’s a good one. In the middle of the query, I’d suddenly just have a new table, and then it’s gone again.
But I’ve merged it with itself.
Now I can go ahead and remove the categories. No, don’t need it.
Patrick Le Blanc:
That’s a good one.
Ville:
I wish I knew that.
Patrick Le Blanc:
I wish I knew that one, too.
Ville:
Now you do.
Tip #3 – Using Scorecards with Usage Metrics (00:03:49 – 00:05:26)
Patrick Le Blanc:
Yes, I do. All right.
Ville:
I can see a bunch of use cases, right?
Anyway, next one up. We’re just going to run them through.
It’s two to one. Two to one. Keep track. Two to one.
Next one, the scorecard that everyone needs and everyone should use regardless of the reporting type.
Here I have a report. I’m in Power BI assets.
I’m going to go ahead and open the usage metric reports that everyone has on every report.
I can see that — let’s see here, it’s loading, it’s loading — there it is.
Only six people are reviewing my report. That sucks. That’s not good.
I mean, those are rookie numbers.
Yeah, those are rookie numbers. I need to get them up.
What I’m going to go ahead and do, I’m going to go back to the workspace, and I’m going to create a new scorecard.
And you’re going to see that when you create a scorecard, you can track to actually do the actionability on your data.
I want to get 20 viewers on this report.
I’m going to set the final target over here to the right, 220.
But the current value I’m going to set to connected data.
And guess what?
What shows up at the very top of my recently selected reports?
Metrics report. Usage rate metrics report.
I can literally use that in my scorecard to track those six users, and it’s going to update every time someone uses a report.
Now I can use this to literally drive usage of my reporting.
This is the one thing that everyone could set up in scorecards because everyone has a report. It doesn’t matter what it consists of.
Patrick Le Blanc:
Okay, three to one.
Ville:
Three to one, yeah.
Patrick Le Blanc:
The fetching is looking pretty bad right here.
To be fair, I haven’t done much with it.
Yes, I haven’t done much with it.
All right, still three to one.
Tip #4 – Nonlinear Numeric Ranges with DAX (00:05:26 – 00:07:06)
Ville:
Is this the final one wins?
The final one wins.
I want to fix it all. Zero, zero. Zero, zero.
We restart now. We restart now.
Patrick Le Blanc:
We’re starting now.
Ville:
Okay, this is it.
Here’s a template that I’ve built actually for Prompt Body, built by two people at Microsoft, one of them in Sweden.
Anyway, this is a Power BI template.
We wanted to have a value calculator.
You can see here, I have three tabs, overview, details, and then the value calculator.
If we go there, I wanted to have a bunch of sliders so you could simulate how much value this was bringing to you if you used it.
But the thing is about the employees here, I wanted this to be able to be used by any company.
Some companies are very small, some companies are very big.
I couldn’t really have a slider that goes from zero to 400,000.
But I can.
I have a slider that goes from one employee up to half a million, basically.
But I don’t have half a million values because then it would have a little eye icon and say, oh, you have too many things.
I don’t want a linear numerical range.
What I did, I created it just like normal, but then you can just open it up and this is just DAX.
It’s just DAX.
I generate one series, one to 19 increments of one, and then increments of five, 20, 100, so I have bigger and bigger leaps.
Patrick Le Blanc:
I knew that one.
You still win. You still win.
Ville:
Fair is fair. Fair is fair.
Four to one. Zero, one, zero.
Anyway, nonlinear numeric ranges. Yeah, that’s amazing.
Patrick Le Blanc:
Yeah, that’s amazing.
So that it?
Closing and Call to Action (00:07:06 – 00:07:42)
Ville:
That it. That’s it.
Patrick Le Blanc:
What do you think?
Is there anything here that you didn’t know?
We’d love to know. Post it in the comments below.
And as always, from Ville and myself, thanks for watching. We’ll see you next time.
See you.