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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
fraserward
Frequent Visitor

DAX to show the Customer_TK in a matrix with lost customer formula

I have used been using some DAX code for new and returning customers from sqlbi, though I am looking for a way to change/update the code to be able to show the Customer_TK in a matrix for Lost Customers.  

 

fraserward_0-1675351761081.png

Three other retention measures working, and when drill down to Customer_TK...

 

fraserward_1-1675351840768.png

Working correctly.  Now I add the Lost Customer measure....

 

fraserward_2-1675351949909.png

This works great with the date, but....

 

fraserward_0-1675352735417.png

Not when trying to see the breakdown with Customer_TK

 

How can I modify this DAX to allow the Lost Customer measure to be filtered by the customer dimension in this matrix.

Here is the DAX...

 

 

Lost Customers = 

VAR LastDateLost = 
    CALCULATE(
        MAX(dim_date[date]),
        ALLSELECTED(dim_date)
    )

VAR CustomersWithLostDate = 
    CALCULATETABLE(
        ADDCOLUMNS(
            VALUES(fact[CUSTOMER_TK]),
            "@LostCustomerDate", [Date Lost Customer]
        ),
        ALLSELECTED(dim_customer),
        dim_date[date] <= LastDateLost
    )

VAR LostCustomers = 
    FILTER(
        CustomersWithLostDate,
        [@LostCustomerDate]
            IN VALUES(dim_date[date])
    )

VAR Result = 
    COUNTROWS(LostCustomers)

RETURN
    Result

 

 

Cheers

2 REPLIES 2
amitchandak
Super User
Super User

@fraserward , refer if my example can help

 

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://www.youtube.com/watch?v=W4EF1f_k6iY

Hi amitchandak,

@amitchandak 


I will check out the video now.  I think your link might be wrong though.  I believe this would be the link you meant... https://www.youtube.com/watch?v=EyL7KMw877Q 

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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