topic Re: Using a parameter in a function in DAX Commands and Tips

Using a parameter in a function

jonbrooker — Tue, 03 Aug 2021 17:14:32 GMT

Hi, I am trying to create a report that calculates how many units are 'live' in a particular year, with the user being able to select the year and the report updating dynamically when the year is changed. Each set of units in my data can be live in either none, one or many years, so I can't simply assign a year to each and use a slicer.

What I have tried to do is the following (I am working in DAX rather than the query editor):

I have created columns that calculate the first year and the last year. This then allows me to calculate if any units are live in a particular year with this formula -

IF(AND(AND([First year]<=2019,[Last year]>2019),

[Last year]>[First year]),

"Yes",

"No")

I can then apply a filter to the table so it only includes the data that returns "Yes" from this formula, giving me the total that I want.

My idea was to replace 2019 in the formula above with the year selected by the user. I thought this would be possible by creating a parameter and replacing the date in the formula with the parameter value ('Parameter'{Parameter value]), but this doesn't seem to work as it appears to use the default value in the calculation rather than the selected value. I think this may be because DAX does not support using parameters in a formula.

Can anyone help me to think of another approach that might allow me to achieve the desired result?

I am still very new to all of this, and I seem to think about things in the wrong way so the approaches I take to solving the problems I encounter are probably not the best!

Any help or guidance on how I can make this work would be gratefully received. If any extra iinformation would help, please let me know.

Many thanks

Note: I have updated this post as I initially sumbitted a draft version by mistake!

Re: Using a parameter in a function

lbendlin — Wed, 04 Aug 2021 00:14:54 GMT

Let go ( a little bit ) of the idea of a "parameter". Power BI has visuals, these visuals have data feeds, and they respond to user interaction. This user interaction then filters all the visuals, and you can base "measures" on the filter choices.

Create a couple of reports to get the hang of it. After that, explore the "What If?" functionality that gives you a ready-made template for modeling behavior.

It helps the other forum users if you provide sample data, and indicate the expected outcome.

Re: Using a parameter in a function

jonbrooker — Wed, 04 Aug 2021 09:23:57 GMT

Hi Ibendlin, many thanks for this. I've not explored What If parameters yet so this definitely offers new possibilities!

However I've tried to implement one in my report and am getting the same behaviour as with a parameter. Hopefully the screenshot below helps a bit.

Each row of data has a number of net units and I want to know if they are live in a specific year. I have fields that tell me the first year and the last year that they are live, which are also shown in the table.

The filter on the table, Parameter check, is my formula that works out if they are live or not:

Parameter check = IF(AND(AND([First year]<='WhatIF'[WhatIF Value],[Last year]>'WhatIF'[WhatIF Value]),

VALUE([Last year])>VALUE([First year])),

"Yes",

"No")

However you can see from the final column that the WhatIF value that is being used by the formula is not changing with the slider, it is always the default (in this case 2016). I need somehow to get the table to dynamically update to show the rows that return yes from my formula based on the value in the slider.

Is there something else I need to do to make this work? If not, is there a totally different approach I could take that would achieve the desired result?

Many thanks again