Excel Lookup Table

[Ms_2]

Hi,

I would like to have a basic user modify their own power BI groups using a simple excel table.

We are trying to group certain activity types (the person deciding what the group number ranges need to be are not computer savvy). 

The Excel file has 3 columns:
Range Start,    Range End,      Group Name
12,000               12,999              Temporary Power Connection

There is a related list of activities which fall into those groups i.e

Activity ID     Description       Cost     Quantitiy

12,500             Crane Costs      $2000   10 hours

Basically, I need to import the excel file, create relationships between the Activity ID and the Range Start &  Range End values to determine the Group Name. 
Not sure how to write the DAX expression

IF(AND(

Activity ID => RELATED(Range Start),

Activity ID = < RELATED(Range End),

Group Name,

"error")

I thought about trying to do a lookupvalue but the Activity ID won't match the Activity ID Range. You could round the number and then match it but then it's not as dynamic anymore.
Am I going about this in the wrong way fundamentally? Any direction would be appreciated thank you.

[v-huizhn-msft]

Hi @Ms_2，

Please refer to solution @Zubair_Muhammad posted, which is right. If you have other issue, please feel free to ask. If you have resolved your problem, welcome to share your solution or mark the right reply as answer. More people will benefit from here.

Best Regards,
Angelia

[Zubair_Muhammad · Accepted solution]

Hi @Ms_2

Assuming table names are Table1 and Table2, add this calculated column in Table2 to get the Group Name

=calculate(
Values(Table1[Group Name]),
filter(table1,
Table2[Activity ID]>=Table1[Range Start]&&
Table2[Activity ID]<=Table1[Range End])
)