customer claim and invoice number

• Question

• Hello. I have a claims table with claims ID numbers. I created a calculated column that returns number of invoices attached to each claim (from another table).

=CALCULATE(COUNTA('Work Orders Real'[WO Number]))

Then, I created another column where it returns sum of invoice amount (total claims for claims with just one invoice).

=if([Work Order Counts]=1,[Claims Cost],blank())

NOW, I would like to return the work order ID number that is associated to the claims with just one work order? How can I do that?

Wednesday, November 16, 2016 10:37 PM

• Hi AlexMartini,

'Work Orders Real' is the invoice table, the relationship between claims table and invoice table is one-may relationship,  right? If it is, you try the following solution.

Create a calculated column in claims table like the formula below.

`=IF([Work Order Counts]=1, LOOKUPVALUE('Work Orders Real'[WO Number],'invoicetable'[ID],'claimtable'[ID]),BLANK())`

I try to reproduce your scenario using the following sample date and get expected result.

First, I create the "Falg count" column which shows the sum of flag to each custom.

`Flag count=COUNTAX(RELATEDTABLE(Table1),Table1[FF])`

Then create "Flag number" column which dispalys the Flag value that is associated to customs with just one flag number.

`Flag number=IF('Custom'[Falg count]=1, LOOKUPVALUE(Table1[FF],Table1[ID],'Custom'[ID]),BLANK())`

Best Regards,
Angelia

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Friday, November 18, 2016 9:23 AM