Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
user01
Helper I
Helper I

Bring 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:

PercentilesValue1Value2Target
1000 
2500 
5010 
7572 
903311 
959336 
98259121 
994622731,400
99.25163351,400
99.46924181,400
99.68215241,400
99.81,136851 
99.91,3151,085 
99.991,932891 

 

Desired result (truncated table to save space):

 

pseriestarget
98.80
98.90
991400
99.11400
99.21400
99.31400
99.41400
99.51400
99.61400
99.70
99.80
99.90
1000
1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community 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]))+0

vzhangti_0-1691733919211.png

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.

View solution in original post

2 REPLIES 2
v-zhangti
Community Support
Community 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]))+0

vzhangti_0-1691733919211.png

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
Super User
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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.