Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to solve a kind of lookup-value problem in PowerBI.
My goal is to calculate a measure that can extract spesific values based on their rownumber (dynamically). The values I want are in the column "Salary", and the number of rows to count down is calculated in the measure "Upper CI".
The table will change when applying filters, but "SerialNO" is a dynamically calculated index. "SerialNO" might be relevant (as a lookup value for Salary - since it is equivalent to the rownumber in the dynamic table) or not.
The best way to describe the problem might be to illustrate it with the desired solution. The table below contain the top 9 rows in my datatable.
So, I am trying to calulate a measure corresponding to the column "Lookup result" (last column in the table). For example in the first row, the value for "Upper CI" is 3, hence I want the third row value from "Salary", that is "362000". In the second row the "Upper CI" is 5, hence I want the fifth row value from "Salary", that is 364 000.
And so on....
Hope someone is able to solve this one.
Thanks so much for any help!
Salary | SerialNO | UpperCI | Lookup result |
350000 | 1 | 3 | 362000 |
360000 | 2 | 5 | 364000 |
362000 | 3 | 6 | 366000 |
364000 | 4 | 8 | |
364000 | 5 | 10 | |
366000 | 6 | 12 | |
371000 | 7 | 13 | |
373000 | 8 | 14 | |
375000 | 9 | 16 |
@Sveina , That can do a new column like
MAxx(filter(Table, Table[SerialNo] = earlier(Table[Upper CI])), Table[Salary])
@amitchandak Thank you, really appreciate your help.
I probably should have mentioned that "SerialNO" and "UpperCI" are measures, not columns in a fixed table. Hence they will be recalculated (and change) when filters are applied. I have also construced a measure for "Salary" (using the function VALUES (Table([Salary])). So all the variabels are actually measures.
I think your solution would have worked with tables, but unfurtunately not with measures. If you have any further suggestions your assistance would be much appreciated!
I am actually trying to construct a dynamic confidence interval around the empirical cumulative distribution function (ECDF) based on data from a salary survey. The methodology is that upper and lower bounds for the confidence interval is a function of the observations rank (when all observations are sorted ascending). The number calculated in the measure "Upper CI" is that ranking thershold value, and "SerialNO" implies whitch salary level (which row in the dynamic table) that corresponds to this threshold.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
25 | |
19 | |
15 | |
8 |
User | Count |
---|---|
58 | |
50 | |
44 | |
21 | |
19 |