Bring in value from one table into another

[user01]

I have two tables (pseries, data_sample). I bring in data_sample via DirectQuery. Basically, I want to bring in the Target value from data_sample into the table pseries in the same range as data_sample. For example, the Target value is defined for Percentiles 99, 99.2, 99.4, 99.6 in data_sample. Then for the table pseries, for all pseries values between 99 and 99.6 (inclusive), I want the Target value to be the same as data_sample, and for all pseries values outside of [99, 99.6] I want the Target value to be 0. How can I accomplish this?

pseries:

pseries = GENERATESERIES(0, 100, 0.1)

data_sample:

Percentiles	Value1	Value2	Target
10	0	0
25	0	0
50	1	0
75	7	2
90	33	11
95	93	36
98	259	121
99	462	273	1,400
99.2	516	335	1,400
99.4	692	418	1,400
99.6	821	524	1,400
99.8	1,136	851
99.9	1,315	1,085
99.99	1,932	891

Desired result (truncated table to save space):

pseries	target
…	…
98.8	0
98.9	0
99	1400
99.1	1400
99.2	1400
99.3	1400
99.4	1400
99.5	1400
99.6	1400
99.7	0
99.8	0
99.9	0
100	0

[v-zhangti · Accepted solution]

Hi, @user01 

You can try the following methods.
Column:

Target = 
Var _MinPercentiles=CALCULATE(MIN(data_sample[Percentiles]),FILTER(data_sample,[Target]<>BLANK()))
Var _MaxPercentiles=CALCULATE(MAX(data_sample[Percentiles]),FILTER(data_sample,[Target]<>BLANK()))
Return
IF([pseries]>=_MinPercentiles&&[pseries]<=_MaxPercentiles,MAX(data_sample[Target]))+0

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@user01 , Try a new column in pseries like

Maxx(filter(data_sample, round(data_sample[Percentiles],1) = pseries[Value]), data_sample[Target])

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8