Examples of Parameters and DAX in Power BI

Create dynamic and flexible Power BI reports

In this video, you will learn about using parameters and DAX in Power BI Desktop. Matt will walk you through an example of how parameters can be powerful, flexible tools to create dynamic visualizations and interactions.

Hi, this is Matt with Playfair+. In today’s video, I’m going to give you an example of how to use a parameter in Power BI and the power it has on your report page. So this example is going to show how parameters can be used to interact with the page. Parameters are really flexible, really valuable, and you can use them for many different ways.

So I hope that this just sparks your imagination, gives you one example of how to use parameters in Power BI. Let’s dive in. We’re going to talk about building out a parameter, and we’re going to do a new parameter. So this is on the modeling tab, and what we’re going to do is we’re going to do a numeric range, and we’re going to call this one, we’ll be timely, we’ll call it, uh, we’ll call it Tariff Adjustment.

And this is going to be a decimal number. We’re going to go between zero and one. We’re going to make it a percentage, and then the increment will be 0.01. So, the option is to automatically add a slicer to this page. You can also add that in later, but we’re going to go ahead and we can do that.

We can add that Tariff Adjustment slicer to the page. And you can see that in your Data pane. There’s also now a new table called Tariff Adjustment. I already had one that was created called Tariff Scenario Play. Let’s go ahead and put a table on, oh, sorry, I didn’t mean to change that type.

Let’s go ahead and put a table on and let’s make sure we’re including our categories. That’s from our Product table, and we’re looking at our Sales Amount from the Sales table. We’re looking at our

Sales Amount, our Total Product Cost, and our Profit, we can use our Profit measure. So now we’re looking at, okay, this makes sense. We’ve got our Sales Amount minus our Product Cost equals our Profit measure, and then we’re going to create new measures that use that tariff that we’ve created. And that goes in the Measures Table.

So we have Tariff Dynamic Cost, so we’re writing DAX, Tariff Dynamic Cost, and we’re going to say equals SUMX() of the Sales tables Sales, Total Product Cost plus the Sales, Total Product Cost times the Tariff Scenario Play value.

Tariff Dynamic Cost = SUMX(Sales, Sales[Total Product Cost] + (Sales[Total Product Cost]*‘Tariff Scenario Play’[Tariff Scenario Play Value]))

And so what this is going to do is it’s going to create a new Dynamic Cost measure, that as we load in you can see right now there’s oh, you know why? Because I’m bringing in the other one. Sorry. The parameter is the previous one that I’ve built. So this will work for you guys. So let’s see here.

Okay. Oh, don’t do this to me. I know we’re out of time. I’m really sorry. We’ll get there. Hold on.

Slicer settings, Single value. Okay, so Single value. So right now what we should be seeing is when it’s set to zero, dynamically the cost is the same, and that would mean that our profit is also going to be the same. But as we change our parameter value using the slicer, you’ll see the cost change and therefore the profit changes.

So you could see here if we wanted to do an analysis on, you know, at what point would a tariff make the AdventureWorks Sales you know, numbers, go from the green into the red. Yeah, we would be at about 13% and all of a sudden our sales have now moved to a net red. So, it’s really powerful to be able to combine something like a parameter with your DAX.

So you can see we just did it in two quick measures. We included the parameter in the cost and that affects what we would do for a dynamic profit calculation. And that would be just one example of the many ways that you can change things dynamically on your screen using parameters and DAX.

This is Matt with Playfair+. Thanks so much for watching our video on parameters. Hope you learned something. Thanks for watching.