Reply
Frequent Visitor
Posts: 14
Registered: ‎04-17-2017
Accepted Solution

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.


Accepted Solutions
Frequent Visitor
Posts: 2
Registered: Sunday

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

View solution in original post


All Replies
Frequent Visitor
Posts: 14
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
-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: 14
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: 1,494
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
Frequent Visitor
Posts: 14
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: 2
Registered: Sunday

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

Frequent Visitor
Posts: 14
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: 2
Registered: Sunday

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:

 

PowerBI.jpg

Frequent Visitor
Posts: 14
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!