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.
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.
Proud to be a Datanaut!
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.
|Claim Number||Allowed Amount||Allowed Amount Match|
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")
Proud to be a Datanaut!
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.
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.
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.
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?