Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
ARODEIRO
Frequent Visitor

Help counting unsubscribed customers in a period

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:

 

ARODEIRO_0-1660918338053.png

ARODEIRO_1-1660918787959.png

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.

1 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.

View solution in original post

5 REPLIES 5
v-yalanwu-msft
Community Support
Community Support

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.

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
Frequent Visitor

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
Resident Rockstar
Resident Rockstar

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:

DateID_CUSTOMERID_INVOICEIS_CANCELLEDCANCELLATION_DATE
01-01-202211TRUE01-01-2022
02-01-202212FALSEnull
02-01-202233FALSEnull


etc

The DAX function would look like this:

 

 

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

 

 

PVO3
Impactful Individual
Impactful Individual

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.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.