Non related table join

DAX DAX Calculations
1

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

2

@dratan99,

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:

https://info.enterprisedna.co/dax-function-guide/lookupvalue/

I hope this is helpful.

  • Brian
3

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

4

@dratan99,

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
5

image
image785×310 7.3 KB

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.
image

7

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.

image
image742×356 8.22 KB

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

1 Like
8

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

9

@dratan99,

Great – glad that worked well for you.

  • Brian