locked
How to check every instance has a match RRS feed

  • Question

  • Hello. How can I check in power pivot that every instance in the many table has a match in the one table. I have a customer table (the one) and a claims table(the money, that has customer id) but I want to make sure that there are no claims/contracts that don't have a match in the customer table. Gracias. 
    Tuesday, August 16, 2016 9:56 PM

Answers

  • You can do a reverse check with RELATEDTABLE. So in your case, with a relationship between Customer (1) and Renewal (many), a calculated column in the Renewal table with

    =RELATED(Customer[ID])

    returns BLANK when there's no corresponding record in Customer. A calculated column in the Customer table with

    =COUNTROWS(RELATEDTABLE(Renewal))

    returns 0 when there's no corresponding record in the Renewal table. Note that RELATEDTABLE returns a table, and must be aggregated to use in a calculated column or a measure (like COUNTROWS, SUMX, etc.)


    Friday, August 19, 2016 7:23 AM
    Answerer

All replies

  • Hi Alex,

    You need to filter out the records that don't have a match in the customer table, right?

    If that is the case, you can use a LOOKUPVALUE function to achieve this requirement. If there is no match that satisfies all the search values, a BLANK is returned. In other words, the function will not return a lookup value if only some of the criteria match. Then filter out the blank values.

    Regards,


    Charlie Liao
    TechNet Community Support

    Thursday, August 18, 2016 10:33 AM
  • Actually, as there's a 'many table' and a 'one table' I assume there's a relationship between the tables. In this case, you can also create a column with =RELATED(Customer[ID]) and achieve the same result.
    Thursday, August 18, 2016 1:14 PM
    Answerer
  • Yes, related works. Thanks. What do you do in a similar case: The tables should be 1 to 1. A customer table and a renewal decision (1 for each customer) in another table. The customer table has some customers that the renewal table does not have (but should) and vice versa. 
    Thursday, August 18, 2016 9:05 PM
  • You can do a reverse check with RELATEDTABLE. So in your case, with a relationship between Customer (1) and Renewal (many), a calculated column in the Renewal table with

    =RELATED(Customer[ID])

    returns BLANK when there's no corresponding record in Customer. A calculated column in the Customer table with

    =COUNTROWS(RELATEDTABLE(Renewal))

    returns 0 when there's no corresponding record in the Renewal table. Note that RELATEDTABLE returns a table, and must be aggregated to use in a calculated column or a measure (like COUNTROWS, SUMX, etc.)


    Friday, August 19, 2016 7:23 AM
    Answerer
  • Thanks Michael. Purrrfect. 

    Friday, August 19, 2016 10:38 PM