cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
domc23 Regular Visitor
Regular Visitor

Looking up vlaues in seperate tables

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.

 

 

11 REPLIES 11
Super User
Super User

Re: Looking up vlaues in seperate tables

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

domc23 Regular Visitor
Regular Visitor

Re: Looking up vlaues in seperate tables

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.

domc23 Regular Visitor
Regular Visitor

Re: Looking up vlaues in seperate tables

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 NumberAllowed AmountAllowed Amount Match
00486JKL000001 $                       1.01Yes
00486JKL000002 $                     13.34No
00486JKL000003 $                     45.67Yes
00486JKL000004 $                     98.76Yes
00486JKL000005 $                     13.22Yes
00486JKL000006 $                     54.76Yes
00486JKL000007 $                     29.00Yes
00486JKL000008 $                     55.87Yes
00486JKL000009 $                     60.74Yes
00486JKL000010 $                     65.61Yes
00486JKL000011 $                     70.48Yes
00486JKL000012 $                     75.35Yes
00486JKL000013 $                     80.22Yes
00486JKL000014 $                     85.09Yes
00486JKL000015 $                     92.45No

                                                                      

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

 

Super User
Super User

Re: Looking up vlaues in seperate tables

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")

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

domc23 Regular Visitor
Regular Visitor

Re: Looking up vlaues in seperate tables

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?

 

 

Super User
Super User

Re: Looking up vlaues in seperate tables

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 Regular Visitor
Regular Visitor

Re: Looking up vlaues in seperate tables

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.

Super User
Super User

Re: Looking up vlaues in seperate tables

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 Regular Visitor
Regular Visitor

Re: Looking up vlaues in seperate tables

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?

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 53 members 1,190 guests
Please welcome our newest community members: