Forum Discussion
Resolver IBring in value from one table into another
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 |
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]))+0Is 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.
2 Replies
- amitchandak
Super User
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 
v-zhangtiCommunity Support
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]))+0Is 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.
