Help counting unsubscribed customers in a period

[ARODEIRO]

I have a table (INVOICES) related to a table (CUSTOMERS) by the column ID_CUSTOMER, and to a table (CANCELATION) by the column PK_INVOICE. The cancellation is the last invoice that the customer had:

I need to count the number of unsubscribed customers in a period , which is filtered from the INVOICES table.
My first attempt was to do:

Unsubscribed = DISTINCTCOUNT(CANCELLATIONS[ID_CUSTOMER])

But since there are rows in the CANCELLATIONS table that are filtered from the INVOICES table, they are excluded from the count.

After doing some research, I found that I could disable the relationship, and filter the cancellations whose period was between the lowest and highest invoice period:

Unsubscribed =
    CALCULATE(
        CALCULATE(
            DISTINCTCOUNT(CANCELLATIONS[ID_CUSTOMER]),
            FILTER(
                CANCELLATIONS,
                ( CANCELLATIONS[DATE] >= FIRSTDATE(INVOICES[DATE])
                    && CANCELLATIONS[DATE] <= LASTDATE(INVOICES[DATE])
                )
            )
        ),
        CROSSFILTER(
            INVOICES[PK_INVOICE],
            CANCELLATIONS[PK_INVOICE],
            None
        )
    )

In this case the account is correct. The problem is that if I filter the invoices by some attribute of the customer (for example, customer type), the number of unsubscribed clients does not change, since there is no longer the relationship between INVOICES (which in turn is related to CUSTOMERS) and CANCELLATIONS.
I tried applying another cross filter between the above result and the CUSTOMERS table, but it still returns the same count:

Unsubscribed =
    CALCULATE(
        CALCULATE(
            CALCULATE(
                DISTINCTCOUNT(CANCELLATIONS[ID_CUSTOMER]),
                FILTER(
                    CANCELLATIONS,
                    ( CANCELLATIONS[DATE] >= FIRSTDATE(INVOICES[DATE])
                        && CANCELLATIONS[DATE] <= LASTDATE(INVOICES[DATE])
                    )
                )
            ),
            CROSSFILTER(
                INVOICES[PK_INVOICE],
                CANCELLATIONS[PK_INVOICE],
                None
            )
        ),
        CROSSFILTER(
            CUSTOMERS[ID_CUSTOMER],
            CANCELLATIONS [ID_CUSTOMER],
            OneWay_LeftFiltersRight
        )
    )

I hope I have explained my problem well. And sorry for my bad English.

[v-yalanwu-msft]

Hi, @ARODEIRO ;

Can you share simple examples and hopefully output results that give you a clearer understanding of your logic?

please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

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

[ARODEIRO · Accepted solution]

Hi, thanks for your response. Finally today I was able to solve my problem. I don't know if it's the most efficient solution, but what I did was add additional filters:

Unsubscribed =
    CALCULATE(
        CALCULATE(
            DISTINCTCOUNT(CANCELLATIONS[ID_CUSTOMER]),
            FILTER(
                CANCELLATIONS,
                (	CANCELLATIONS[DATE] >= FIRSTDATE(INVOICES[DATE])
                    && CANCELLATIONS[DATE] <= LASTDATE(INVOICES[DATE])
					&& CONTAINS(CUSTOMERS, CUSTOMERS[ID_CENTROOPERATIVO], CANCELLATIONS[ID_CENTROOPERATIVO])
					&& CONTAINS(CUSTOMERS, CUSTOMERS[SUCURSAL], CANCELLATIONS[SUCURSAL])
					&& CONTAINS(CUSTOMERS, CUSTOMERS[ID_ZONA], CANCELLATIONS[ID_ZONA])
					&& CONTAINS(CUSTOMERS, CUSTOMERS[SEGMENTO], CANCELLATIONS[SEGMENTO])
                )
            )
        ),
        CROSSFILTER(
            INVOICES[PK_INVOICE],
            CANCELLATIONS[PK_INVOICE],
            None
        )
    )

With this I was able to get the desired result.

[ARODEIRO]

It is really very hard to explain (and understand) what I need. Cancellations are not the date the invoice was canceled, but the last invoice issued to a customer who had no invoices in the following two months. So the customers don't have a single cancel date, so I can't add a column to the customer (or the invoice) with the cancel date. I need the one-to-one relationship because in another dashboard I show data from the last invoice issued to each canceled customer.

[Daryl-Lynch-Bzy]

Hi @ARODEIRO  - I am wondering if you could simplify you Data Model to make the DAX easier.  You could consider changing your INVOICE table to show the following:

Date	ID_CUSTOMER	ID_INVOICE	IS_CANCELLED	CANCELLATION_DATE
01-01-2022	1	1	TRUE	01-01-2022
02-01-2022	1	2	FALSE	null
02-01-2022	3	3	FALSE	null

etc

The DAX function would look like this:

Unsubscribed = 
CALCULATE (    
    DISTINCTCOUNT(INVOICE[ID_CUSTOMER]) ,
    KEEPFILTERS( INVOICE(IS_CANCELLED) )
)

[PVO3]

Hello,

Unfortunatly it's hard to understand what you are trying. Seems like first of all you should create a star model by merging Invoices & Cancellations as they have a 1 to 1 relation for a proper fact table. Then create a date table to define your scope.

Ps. I think using REMOVEFILTERS is better suited for your problem then CROSSFILTER none.