cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Anonymous
Not applicable

## Re-scaling a parameterized measure for each row in data

Hi all,

I built a simple index for each row in my data set that is a weighted linear combination of several columns. The weights are what-if parameters that the user can input and adjust to see the index change on the fly. Now, I want to rescale this raw index into a score out of 100. Conceptually, the mathematics for that are simple:

{Index - MIN(Index) / MAX(Index) - MIN(Index)} * 100

However, I'm having trouble implementing this as a measure that will calculate and change on the fly when the user updates the parameters.

Here is the weighted linear combination as a measure:

I tried using variables to yield the scores out of 100 for each row, but what I have currently yields NaN values.

Any thoughts on how I might be able to get to the scores and keep them dynamic so they change when the parameters are adjusted?

Thanks

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Just following up on this with the solution. The issue is with the min/max calculations. MINX and MAXX are evaluated over every row in the data where the measure is calculated. They don't "anchor" to the max/min across all rows. Had to nest an all function:

maxx(all(ta_vital),[TAVI_RAW]). Once you do that for min/max, the calculation works and the data is rescaled out of 100.
4 REPLIES 4
Anonymous
Not applicable

Just following up on this with the solution. The issue is with the min/max calculations. MINX and MAXX are evaluated over every row in the data where the measure is calculated. They don't "anchor" to the max/min across all rows. Had to nest an all function:

maxx(all(ta_vital),[TAVI_RAW]). Once you do that for min/max, the calculation works and the data is rescaled out of 100.
Resolver III

Nothing else stands out as the possible cause of the issue. Your use case (in measures) is basically what what-if parameters are for.

Probably not the issue... but one thing that jumps out at me is the use of SUMX and MAXX/MINX. Because you're just looking at a single value in the table SUM() and SUMX() should return the same thing, as would MINX/MAXX.
What I would do at this stage, is test the return values of each of the VARs you've declared to narrow down the source of the issue.

Resolver III

If you're creating this as a 'Column' in DAX, column evaluation is done once when the data is loaded. Changing a parameter will not cause the table to re-evaluate.

Referring to parameter values in a column calculation will yield BLANK() values, which is why your calculations are returning NaNs.

You may be able to implement this as a MEASURE instead, but it depends on what you plan to do with it.

Anonymous
Not applicable

Yeah I did start with attemtping a calculated column for everything but like you said, columns added to the data will not update on the fly. I'm trying to do all of this using measures. I think part of the problem is telling PowerBI to duplicate the 0/100 scoring for each value on an axis. I can create some simple measures for Min/Max. The trouble starts when I try to perform the mathematics with those measures and the "i-th" value of the raw index.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors