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 have a problem. I would like to lookup a value in the dataset. The values are sorted from lowest to highest value.
The position of the value that has to be looked up is calculated, by the folowing measure:
Position value that has to be selected = ROUND([Variable A] - (1,5*[Variable B]);0)
where:
Variable A = DISTINCTCOUNT(data[Date])
and
Variable B = [Variable A]/365
In the report there are 2 filters:
- location filter to select the location
- date-slicer
Result of the report is the value at the position of the index that equals the result of measure "Position value that has to be selected". (e.g. Result of measure "Position value that has to be selected" = 6. Another measure has to lookup the value at position 6 in the dataset)
I think I have to make a dynamic index column and a lookup measure, but I can't get it work.
The example PBI-report and dataset can be downloaded via the following link:
https://www.dropbox.com/sh/3qve0rh45bfxk9h/AAAJjZ0T7awhcfjhiuCYBT9ea?dl=0
It is a small part of the complete dataset I use, but I think it will be enough for answering my quenstion
In tab 1 of the PBI-report there is a table of the dataset and the Query that manipulated the data in PowerQuery. In tab 2 there is the example of te report
Thanks
Solved! Go to Solution.
@Anonymous,
Please check DAX in the PBIX file below.
https://1drv.ms/u/s!AhsotbnGu1NolA-pldfc-xnQJyjE
Regards,
Lydia
@Anonymous,
You would need to firstly rank your table visual based on Value field, then compare the rank value to the position value to get expected row.
Regards,
Lydia
@Anonymous,
If I understand you correctly, you just need to create a measure as follows.
maxaverage = MAX(data[Average_Value])
More details in this PBIX file: https://1drv.ms/u/s!AhsotbnGu1NolAxYwAt3tipzk9zF
Regards,
Lydia
Hello @v-yuezhe-msft Lydia,
Thanks for your answer.
It is not always the maximum value. I've added a larger dataset to the PBIX-file: https://www.dropbox.com/s/xk2n9gxio8le0qv/Example.zip?dl=0
As you can see, the position of the value that I need is most of the time not the maximum. for example: at location ADCP216B_V1 there are 672 values. The value that I need is at position 669 in the data table.
Regards,
Paul
@Anonymous,
Please check DAX in the PBIX file below.
https://1drv.ms/u/s!AhsotbnGu1NolA-pldfc-xnQJyjE
Regards,
Lydia
Thanks. I think it is the solution of my question. Can you explain for me what you did (and why). That helps me with checking the results.
@Anonymous,
You would need to firstly rank your table visual based on Value field, then compare the rank value to the position value to get expected row.
Regards,
Lydia
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |