March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
Solved! Go to Solution.
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:
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:
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.
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
121 | |
99 | |
88 | |
70 | |
61 |
User | Count |
---|---|
142 | |
122 | |
105 | |
94 | |
90 |