Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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
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#]))
@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#] ) )
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.'
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
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.
Hello Evan,
The first Calc column expression achieves those results. I have recreated your tables here and successfully brought in Codes:
Darn Typo! You are absolutely correct! I am officially good now. Thank you so much for all your assistance!
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
108 | |
108 | |
93 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |