Compare table 1 int to range in table 2. Return value in table to of same row as range.

[Anonymous]

I'm attempting to create a custom column in table 1 that compares a value in table 1 to 2 values in table 2. Esentially a range. The 2 values in table 2 are within the same row. If the value is within the range I want to return another column [Location] from that same row as the range in table 2.

This is how far I've gotten. Any ideas?

Table.SelectRows(table2, each [Range_Start_Int] < [IP_Int] and [Range_End_Int] > [IP_Int])

[v-xuding-msft]

Hi @Anonymous ,

Please try this code:

= Table.SelectRows(#"Table 1", (Magic) =>  [Range_Start_Int] < Magic[IP_Int] and [Range_End_Int] > Magic[IP_Int])

Reference:

PowerQuery(M)agic: Conditional Joins using Table.SelectRows() 

Best Regards,

Xue Ding

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

[amitchandak]

Try like

New column in Table 1 = maxx(filter(table2,table1[ID] = table2[ID] && table2[Range_Start_Int] < table1[IP_Int] && table2[Range_End_Int] > table1[IP_Int]),table2[location])

Add or remove columns as per need.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[VasTg · Accepted solution]

@Anonymous 

Ideally you should expand the table where you have the range and join with other table to support enterprise scale.

The other approach is what you are asking for..

Refer to these posts.

https://community.powerbi.com/t5/Desktop/Dax-SUM-hours-from-another-table-based-on-valid-from-to-dates/m-p/845060

If it helps, mark it as a solution

Kudos are nice too

[Anonymous]

@VasTg  Thanks for taking the time to reply. This solved my problem. Being new to Power BI I did not realize I could add a column in Report View and was attempting to complete the task in the Data View.