Looking up vlaues in seperate tables

[domc23]

Hello, I am new to the forum and I will try to explain what I am looking to do the best way I know how.

I have two data tables, one called Claims and the other called RBP. On the Claims table, there is a column called Client Ref# and also a column called AllowedAmt. On the RBP table, there is a column called Claim Number and a column called Allowed Amount. What I am hoping to do is have a query look to match the Client Ref# on the Claims table to the Claim Number column on the RBP table and if it finds a match to then compare the AllowedAmt column on the Claims table to the Allowed Amount on the RBP table. If those amounts match, then report back a "Yes". If they do not match, then report back a "No".

Ultimatly, I am looking to see if the allowed amounts are the same or different for the same claim number across the two tables. I hope this makes sense. 

Any direction with this is greatly appreciated.

[Greg_Deckler]

So, you would likely want a relationship between those two tables and then in your Claims you could use a custom column with a RELATED statement to check if the amount is greater than the related amount in your other table. Sample data would help get to a more specific solution.

[domc23]

Here are the sample data from the two tables. Sorry about how this is viewing, I need to learn how to post samples.

The hope is to compare Claim Ref#  and the AllowedAmt from Table1 to the Claim Number and Allowed Amount from Table2. I have added a new column with a Yes or No value depending on what the query finds. IF there is a match on a claim number AND there is a match on the allowed amount from both tables, then I would like to see a Yes value retured. If there is a match on a claim number and the allowed amounts DO NOT match, them I would like to see a No value returned.

This probably does not help you at all but hopefully it will shed a little more light on what I am attempting to do. If you know of a simplier way to to accomplish this, please let me know.

Thank you in advance.

Table 2
Claim Number	Allowed Amount	Allowed Amount Match
00486JKL000001	$                       1.01	Yes
00486JKL000002	$                     13.34	No
00486JKL000003	$                     45.67	Yes
00486JKL000004	$                     98.76	Yes
00486JKL000005	$                     13.22	Yes
00486JKL000006	$                     54.76	Yes
00486JKL000007	$                     29.00	Yes
00486JKL000008	$                     55.87	Yes
00486JKL000009	$                     60.74	Yes
00486JKL000010	$                     65.61	Yes
00486JKL000011	$                     70.48	Yes
00486JKL000012	$                     75.35	Yes
00486JKL000013	$                     80.22	Yes
00486JKL000014	$                     85.09	Yes
00486JKL000015	$                     92.45	No

Table 1
Client Ref#	AllowedAmt
00486JKL000001	$              1.01
00486JKL000002	$           12.34
00486JKL000003	$           45.67
00486JKL000004	$           98.76
00486JKL000005	$           13.22
00486JKL000006	$           54.76
00486JKL000007	$           29.00
00486JKL000008	$           55.87
00486JKL000009	$           60.74
00486JKL000010	$           65.61
00486JKL000011	$           70.48
00486JKL000012	$           75.35
00486JKL000013	$           80.22
00486JKL000014	$           85.09
00486JKL000015	$           89.96

[Greg_Deckler]

Can't figure out why this works, seems like the greater than/less than is opposite of what it should be.

Allowed Amount Match = IF([ AllowedAmt ] >= RELATED(RDB[Allowed Amount]),"Yes","No")

[domc23]

Smoupre, How would I get this formula to report a third value of "NP" for not processed yet? It seems that the formula is reporting a "Yes" for all allowed matches as well as any claim number that is not found. I need to add the third value to account for when we cannot find a claim number match.

Any ideas?

[domc23]

Interesting!

How is this doing a claim reference by claim reference comparison first to find a match to then go and look at the allowed amounts?

[Ashish_Mathur]

Hi @domc23,

Assuming there will never be duplicate Client Ref# on Table 1, first create a relationship from Client Number on Table 2 to Client Ref# on Table1.  Then the suggested formula should work.

[domc23]

Thank you for your response.

I have already created the releationship between the two tables. There should not be a dublicate claim number in table 1 so we should be good. I am using a Attribute Slicer to control two seperate graphs. That seems to work just fine. When I select a claim number from that slicer, both graphs will only display that claim number. The issue I am having is I have setup another slicer to display the Yes or No results from the above formaula and when I select either, only the table with that column that was added (Table1) will display the correct claim numbers. Table2 will not adjust to display the correct Yes or No selection. I am looking to have the Yes - No slicer control both graphs that I have setup.

[Ashish_Mathur]

Hi.

In that case, you must create a Yes,No column in Table1 as well using the LOOKUPVALUE() function.  Then you will have to create another table (Table3) with only two values - Yes and No.  The Yes/No columns of Table1 and Table2 will need to have a relatioship to Table3.  In the slicer, you will drag the Yes/No column from Table3.

[domc23]

Thank you Anish,

I am trying the Lookupvalue function but it is saying I need 3 arguments for it to work. I entered the following into table2.

Results = LOOKUPVALUE(ClaimsSLAReport[Client Ref #],ClaimsSLAReport[Allowed Amount Match])

With the Client Ref# and Allowed Amount Match being on Table1,

The hope would be to have the Lookupvalue function look at the Claim Ref # and then report what the associated Yes / No response would be from the Allowed Amount Match column. This does not work because of the 3 arguments that Lookupvalue is looking for. What am I missing?

[domc23]

Trying a different approach. I have the Yes and No working now but what I am missing is a third response from the table 1 column Allowed Amount Match. I need a value of "NP" to display for any claim that has not been processed. I am trying the below code which is not working. I know that I am missing something probably very simple but I cannot get the third value of NP to work.

Allowed Amount Match = OR(IF(ClaimsSLAReport[AllowedAmt] >= RELATED(RBP[Allowed Amount]),"Yes","No"),IF(ClaimsSLAReport[AllowedAmt] >= RELATED(RBP[Allowed Amount]),"Yes","No","NP")))

How do I get NP to be a value when there is not a Claim Number match?

What I want is a Yes or No for a claim number that we find a match on, but when there is no common claim number on both tables, I need an NP to display. 

Help!!!

Any idea how to get this to work?

[domc23]

Thank you for your response. Let me get you some sample data and see if that will help explain what I am looking to do a little better.