Unique Count of record in one table only if it doesn't exist in another table

[Mike282]

Hi I've got a DAX question in relation to doing a specific calculation. I've got 2 data tables. The first table is called "Payments" which stores charge attempts and their status (e.g. Failed or Succeeded). Each charge has a ID for the invoice it sits against which itself sits in a Invoice table called Customer Invoice. The Payment table will create 1 record for each time a charge attempt is done on the invoice (based off the IN_ID) to a total of 4 times. The Payment table and Invoice table should technically be joined as a Many to 1 from Payment to Invoice but I noticed if the payment fails after 4 charges the invoice no longer exists in the Invoice table (as the invoice is now cancelled) therefore I can't match all the charge values to a corresponding invoice to the invoice table. 

What I want to do is do is know how many invoices were cancelled based on the Payment table where the charge numbers are stored. Is there a DAX that can do a unique count based on an IN_ID not matching or not existing in the main invoice table? Below is an example:

Payment Table

You can see in the payment table there are several charges made with a charge status, date stamp of when the charge was made and the column called IN_ID which contains the invoice ID that will match to the Customer Invoice table. Note that among the invoices only charges with IN_ID of 11, 22 and 55 will appear in the Customer Invoice table as they contain a succeeded charge.

Customer Invoice Table

The Customer Invoice table containing the invoice and customer name. Note again that only 11, 22 and 55 appears here as they were the only invoices which succeeded in the charge eventually. whilst charge 33 and 44 does not appear here. I want to count only charges in the Payment table if it doesn't exist in the Customer Invoice table to understand total actual cancelled as opposed to those which has a few failed payments but eventually succeeds.

Another thing is that I'll need to be able to filter this by date so if someone asks me for a number of all true failed payments (one where it doesn't appear in the Customer Invoice table) I can do it by date e.g. if 4 charges all happen within the month of February then it counts as 1 failed/cancelled payment.

Any help would be greatly appreciated.

Kind regards,

Mike

[v-juanli-msft · Accepted solution]

Hi @Mike282 

Create calcuated columns in Payment table

related = RELATED(customer[id])

new related = IF([related]=BLANK(),0,[related])

Then create a measure to count the failed id

count_failed_measure = CALCULATE(DISTINCTCOUNT(payment[id]),FILTER(ALLSELECTED(payment),[new related]=0))

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mike282]

Awesome Maggie,

That worked perfectly! Thank you!

Kind regards,

Michael

[Mike282]

So I've got a solution to this but would like some help from the forum. Instead of doing a unique count on charges I decided to pass the latest Charge status from the Payments table as a Calculated Column onto the Customer Invoice table. What ends up happening is if the payment eventually succeeds it will have the status of succeeded but if it has failed the column would just be null. How to I set it so that it passses "Failed" as a value to the column if it returns no value? Also I set the DAX to concatenate in case there is 2 of the same IN_ID in the Customer invoice table that could cause issues with Many to Many.

Payment Status = 
VAR mylastdate =
    MAXX (
        FILTER (
            Payments,
            Payments[IN_ID] = CustomerInvoice[IN_ID]
        ),
        Payments[Created]
    )
RETURN
    CONCATENATEX (
        FILTER (
            Payments,
            Payments[IN_ID] = CustomerInvoice[IN_ID]
                && Payments[Created] = mylastdate
        ),Payments[Status],","
    )

Also using a similar logic to the DAX above to pass across a count of attempts (Payments) as a number into a calculated column in CustomerInvoice based on the logic of matching the IN_ID to perform the count.

Kind regards,

Mike