## Desktop

Regular Visitor
Posts: 20
Registered: ‎04-17-2017

# LookupValue from Table A with a Num within a range of numbers determined from a h/l from Table B

This Question has me scratching my head.

I have a Table with an Endorsement Category in Table A.  In Table A I am given a Hi and Low ID#.

In Table B I have an entry for what the charge amount is for the endorsement in Table A.

How do I setup a Dax expression which will take the mystery Fee amount's ID number and find the row which is within the Endorsement codes Hi/Low amounts as long as it maintains the table's relationship as determined by the common rootID#.

Accepted Solutions
Frequent Visitor
Posts: 3
Registered: ‎04-23-2017

## Re: LookupValue from Table A with a Num within a range of numbers determined from a h/l from Table B

For a column in Table B (fetching Code from Table A):

Column = CALCULATE(Values('A'[Code]),Filter('A','A'[Id#] <= 'B'[Id#] && 'A'[LastId#] >= 'B'[Id#] && 'B'[RootId#] = 'A'[RootId#]))

For a column in Table A (fetching FinalCharge from Table B):

Column = CALCULATE(Values('B'[FinalCharge]),Filter('B','B'[Id#] >= 'A'[Id#] && 'B'[Id#] <= 'A'[LastId#] && 'A'[RootId#] = 'B'[RootId#]))

but please note that you will get a "circular dependency" error if you try to create both expression columns in this scenario

All Replies
Regular Visitor
Posts: 20
Registered: ‎04-17-2017

## Re: LookupValue from Table A with a Num within a range of numbers determined from a h/l from Table B

Here is what I have so far.

Facts:

Table A

 RootId# Id# LastId# Code -2147479362 2095666418 2095666425 A -2147479362 2095666426 2095666433 B -2147479362 2095666434 2095666441 C -2147479362 2095666442 2095666449 D -2147479362 2105134280 2105134287 E

Table B

 RootId# Id# LastId# FinalCharge -2147479362 2095666419 2095666419 50 -2147479362 2095666427 2095666427 50 -2147479362 2095666435 2095666435 259.34 -2147479362 2095666443 2095666443 50 -2147479362 2105134281 2105134281 259.34

As you can see RootId# is the relationship value each order is tied to.  I am attempting to add a column in table B to take the Value of ID# and look in Table A Minimum of ID# and Maximum of LastId#.

Here is the code I have so far.

Code = CALCULATE(Values('A'[Code]),Filter('A','B'[Id#] >= 'A'[Id#] && 'B'[Id#] < 'A'[LastId#]))

Regular Visitor
Posts: 20
Registered: ‎04-17-2017

## Re: LookupValue from Table A with a Num within a range of numbers determined from a h/l from Table B

I have managed to secure some SQL which actually does what I am attempting to convert to DAX.  I am still not able to make my expression work but I hope someone else here may be able to assist.

SELECT

E.RootId#,

E.Code,

CC.FinalCharge

FROM pfm.Endorsement as E

INNER JOIN pfm.ChargeCalculation as CC

ON E.RootId# = CC.RootId#

WHERE

CC.ID# BETWEEN E.Id# AND E.LastId#

End result will be all the codes paired with the correct fee associated with it.

Moderator
Posts: 3,051
Registered: ‎03-06-2016

## Re: LookupValue from Table A with a Num within a range of numbers determined from a h/l from Table B

TM_Evan wrote:

Here is what I have so far.

Facts:

Table A

 RootId# Id# LastId# Code -2147479362 2095666418 2095666425 A -2147479362 2095666426 2095666433 B -2147479362 2095666434 2095666441 C -2147479362 2095666442 2095666449 D -2147479362 2105134280 2105134287 E

Table B

 RootId# Id# LastId# FinalCharge -2147479362 2095666419 2095666419 50 -2147479362 2095666427 2095666427 50 -2147479362 2095666435 2095666435 259.34 -2147479362 2095666443 2095666443 50 -2147479362 2105134281 2105134281 259.34

As you can see RootId# is the relationship value each order is tied to.  I am attempting to add a column in table B to take the Value of ID# and look in Table A Minimum of ID# and Maximum of LastId#.

Here is the code I have so far.

Code = CALCULATE(Values('A'[Code]),Filter('A','B'[Id#] >= 'A'[Id#] && 'B'[Id#] < 'A'[LastId#]))

It seems that you're missing one filter on RootId#, just try

```Code =
CALCULATE (
VALUES ( 'A'[Code] ),
FILTER (
FILTER ( 'A', A[RootId#] = B[RootId#] ),
'B'[Id#] >= 'A'[Id#]
&& 'B'[Id#] < 'A'[LastId#]
)
)
```
Highlighted
Regular Visitor
Posts: 20
Registered: ‎04-17-2017

## Re: LookupValue from Table A with a Num within a range of numbers determined from a h/l from Table B

I tried the suggestion but unfortunately it did not work.  Get a response 'The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.'

Frequent Visitor
Posts: 3
Registered: ‎04-23-2017

## Re: LookupValue from Table A with a Num within a range of numbers determined from a h/l from Table B

For a column in Table B (fetching Code from Table A):

Column = CALCULATE(Values('A'[Code]),Filter('A','A'[Id#] <= 'B'[Id#] && 'A'[LastId#] >= 'B'[Id#] && 'B'[RootId#] = 'A'[RootId#]))

For a column in Table A (fetching FinalCharge from Table B):

Column = CALCULATE(Values('B'[FinalCharge]),Filter('B','B'[Id#] >= 'A'[Id#] && 'B'[Id#] <= 'A'[LastId#] && 'A'[RootId#] = 'B'[RootId#]))

but please note that you will get a "circular dependency" error if you try to create both expression columns in this scenario

Regular Visitor
Posts: 20
Registered: ‎04-17-2017

## Re: LookupValue from Table A with a Num within a range of numbers determined from a h/l from Table B

I tried the suggestions Patina and did not get the results I was hoping for.  Thank you for the suggestions just the same.

I am hoping to either add the Code field to the FInal Charge field or vice versa.  either result will allow me to dispay in it report tables.

Frequent Visitor
Posts: 3
Registered: ‎04-23-2017

## Re: LookupValue from Table A with a Num within a range of numbers determined from a h/l from Table B

Hello Evan,

The first Calc column expression achieves those results. I have recreated your tables here and successfully brought in Codes:

Regular Visitor
Posts: 20
Registered: ‎04-17-2017

## Re: LookupValue from Table A with a Num within a range of numbers determined from a h/l from Table B

Darn Typo!  You are absolutely correct!  I am officially good now.  Thank you so much for all your assistance!