select the top values!

[d904490]

trying to select only the values which are 12 less than 160. example- name = 160, output = 160; otherwise take name whaich are less than or equal to ()160-12. I also have another table which returns this current target value i.e. 160 or the changed value if changes in future. so I try to pick the name values which are 12 less than my target. I am new to power bi, need urgent help, please.

[v-lili6-msft · Accepted solution]

Hi,@d904490 

    you can use this formula to add a column as like below:

Top5 = IF(CALCULATE(MAX(Table3[current match number]))-Table2[match_number]<=5,"Top 5")

basic data

table1

table2

Then you can filter the column Top5

Best Regards,

Lin

[d904490]

Thanks. It worked for me!

[v-lili6-msft]

hi,@d904490 

    Your description is not clear, Please show us your desired result in the data sample? you can provide some data sample and the expected output or the pbix .You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. 

Best Regards,

Lin

[d904490]

please find the sample data and the desired output attached below.

Table 1:	return the current match number. Example the current match number is 160 and next week  it will change to 161 and every week it changes to 1+
table 2:	id, enddate, startdate, match number
id	endate	start date	match_number
1	29/06/2018	25/06/2018	151
2	6/07/2018	2/07/2018	152
3	13/07/2018	9/07/2018	153
4	20/07/2018	16/07/2018	154
5	27/07/2018	23/07/2018	155
6	3/08/2018	30/07/2018	156
7	10/08/2018	6/07/2018	157
8	17/08/2018	13/07/2017	158
9	24/08/2018	20/07/2018	159
10	31/08/2018	27/072018	160
Output:	top 5 match_number <= current_match. i.e. only top 5 match_number <=160
5	27/07/2018	23/07/2018	155
6	3/08/2018	30/07/2018	156
7	10/08/2018	6/07/2018	157
8	17/08/2018	13/07/2017	158
9	24/08/2018	20/07/2018	159
10	31/08/2018	27/072018	160