Reply
Frequent Visitor
Posts: 12
Registered: Monday

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

 

Thanks in Advance.

Frequent Visitor
Posts: 12
Registered: Monday

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
-214747936220956664182095666425A
-214747936220956664262095666433B
-214747936220956664342095666441C
-214747936220956664422095666449D
-214747936221051342802105134287E

 

Table B

RootId#Id#LastId#FinalCharge
-21474793622095666419209566641950
-21474793622095666427209566642750
-214747936220956664352095666435259.34
-21474793622095666443209566644350
-214747936221051342812105134281259.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#]))

Frequent Visitor
Posts: 12
Registered: Monday

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: 1,478
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#]
    )
)
Frequent Visitor
Posts: 12
Registered: Monday

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