# Non related table join

Hi, I am calculating %availability using a measure on tblDevice based on total downtime/availability . I want to join a static table tblAllowance which has allowance based on the %availability. How can I join ? I tried to do merge join between two tables , I couldn’t see %availability column to match as its a measure. Please advice.

static table tblAllowance

|decimal| availabiltity| allowance|
|0.9882| 98.82%| -2|
|0.9883| 98.83%| -1|
|0.9884| 98.84%| 0|
|0.9885| 98.85%| 7|
|0.9886| 98.86%| 8|
|0.9887| 98.87%| 9|
|0.9888| 98.88%| 10|
|0.9889| 98.89%| 11|
|0.9890| 98.90%| 12|
|0.9891| 98.91%| 13|

for Example, if %availability is 98.85% then
tblDevice should return as below
Device Availablity allowance
1 98.85 % 7(should return)

Thanks

To get the result you’re looking for, you don’t need to join the tables. The LOOKUPVALUE() function will easily handle this requirement. Here’s the entry from the eDNA KnowledgeBase:

• Brian

Hi Brian, I tried with lookup. It didn’t allow me to use measure in lookup.

That’s right - sorry, answered too quickly just looking primarily at the table in your post.

Can you please post your PBIX file? I have a couple of other ways we can handle this, but I want to test them on your data/measures/data model.

Thanks.

• Brian

Hi Brian,
I tried to put in excel sheet tbldevice , I made some dump data as not able to share pbix. In Cell D is a measure calculating dowtimehours/actualhours and it gets %. I am trying to compare this % with 2nd table and get allowance from 2nd table. 2nd table have around 30,000 rows values for all % values.

Okay, based on your sample data I constructed the following example. First I created a disconnected table based on the values of Availability, and then just harvested the selected value from that table just to have a measure to work with in the example.

The measure that does the lookup is as follows:

``````Credit Bonus Lookup =
CALCULATE(
MAX( Data[Credit/Bonus] ),
FILTER(
Data,
Data[Availability] = [Harvest Downtime%]
)
)
``````

This basically mimics the function of what LOOKUPVALUE() does. Note that in your example, one value of Availability is associated with more than one credit/bonus value in some cases. Thus in my measure I returned the MAX value of credit/bonus value, however based on your business rules you may want to change this to MIN.

I hope this is helpful. Full solution file attached below.

1 Like

Thanks Brian for a quick response. I used your solution. It works.

Great – glad that worked well for you.

• Brian