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

[TM_Evan]

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

Thanks in Advance.

[TM_Evan]

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#]))

[Eric_Zhang]

---

@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#]
    )
)

[TM_Evan]

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

[Patina · Accepted solution]

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

[TM_Evan]

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.

[Patina]

Hello Evan,

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

[TM_Evan]

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

[TM_Evan]

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.