Lookup a value in the dataset by calculating the position of the looked up value in the dataset

[Anonymous]

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

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Please check DAX in the PBIX file below.

https://1drv.ms/u/s!AhsotbnGu1NolA-pldfc-xnQJyjE

Regards,
Lydia

[v-yuezhe-msft]

@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

[Anonymous]

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

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Please check DAX in the PBIX file below.

https://1drv.ms/u/s!AhsotbnGu1NolA-pldfc-xnQJyjE

Regards,
Lydia

[Anonymous]

@v-yuezhe-msft

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.

[v-yuezhe-msft · Accepted solution]

@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