Distinct counting across multiple relationship tables

[masplin]

Struggling with the syntax having reorganized my tables as recommended. my tables look like this

Transactions INVNUM Service 123 MOT 123 LUBE 123 AIR 567 MOT 567 LUBE 567 AIR <tbody> </tbody>

Invoices INVNUM RegNum Surname Postcode Unique customer 123 OE08DYO Smith AL1 3QB AL1 3QB - Smith 567 OE08DYO Smith AL1 3QB AL1 3QB - Smith <tbody> </tbody> Customers Unique Customer MOT Count Unique MOT Vehicles AL1 3QB - Smith 2 1 <tbody> </tbody> So each unique customer has multiple INVNUM and each INVNUM has multiple transactions. transaction[invnum] is related to invoices[invnum] and invoices[unique customer] is related to customers[unique customer] To calculate MOT I needed to count the number of transaction lines with "MOT" where the INVNUM linked to the Custoter via the Invoices relationships. I managed ot work this out as <tbody> </tbody>

<tbody>
</tbody>

=sumx(RELATEDTABLE(Invoices),CALCULATE(COUNTROWS(Transactions),filter(Transactions,Transactions[INVNUM]=Invoices[INVNUM]  && Transactions[Service]="MOT" )))

So far so good. However I also wanted to be able to count how many different vehicles the customer had brought in for MOT using unique RegNum. In this case he has brought the same vehicle twice so the answer should be 1. I have tried everything, but cannot work out how to calculate this. in my first version of the model I had the RegNum and customer details in the transaction table so could use.

=CALCULATE(DISTINCTCOUNT(Transactions[RegNum]),filter(Transactions,Transactions[Unique Customer]=Customers[Customer Code] && Transactions[Service]="MOT"))

I tried various things including this, but this just gave me the same as the MOT Count i.e. =2.

=sumx(RELATEDTABLE(Invoices),CALCULATE(DISTINCTCOUNT(Invoices[RegNum]),filter(Transactions,Transactions[INVNUM]=Invoices[INVNUM] && Transactions[Service]="MOT")))

Is this possible or do I have to RegNum back into the transaction table and use say DISTINCTCOUNT(transactions[regnum])?

This is all new to me having a triple layer cake so any advice appreciated.

Mike

 

[jk-vtti]

Perhaps this post will help: http://www.mrexcel.com/forum/powerp...s-true-false-if-duplicate-value-new-post.html

 

[masplin]

Thanks but I dont see the connection. When i had just 2 tables with a relationship between the formula was simple. I now have 3 tables with 2 relationships and that is where I am struggling. The "MOT" data sits in one table and the "regnum" filters sits in the 2nd table. So essentially i am filtering table 1 based on "MOT" then counting the disitinct "REGNUM" for those invoices that match both the "MOT" transactions and the unique customer.