cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
pbertens Regular Visitor
Regular Visitor

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

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

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

@pbertens,

Please check DAX in the PBIX file below.

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Moderator v-yuezhe-msft
Moderator

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

@pbertens,

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
Moderator v-yuezhe-msft
Moderator

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

@pbertens,

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
pbertens Regular Visitor
Regular Visitor

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

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

Moderator v-yuezhe-msft
Moderator

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

@pbertens,

Please check DAX in the PBIX file below.

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

pbertens Regular Visitor
Regular Visitor

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

@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.

 

Moderator v-yuezhe-msft
Moderator

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

@pbertens,

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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 274 members 1,991 guests
Please welcome our newest community members: