cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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: 

 

conreed_1-1659643127804.png

 

 

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

 

conreed_0-1659643050362.png

 

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. 

View solution in original post

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. 
MisterFry
Resolver III
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. 

MisterFry
Resolver III
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. 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.