Count

[manideep547]

Hi all,
We have to show the count of customers. In my table I am having Customer ID, Zone Category values are (Retail, F&B, L&E) Count of each unique customer who started in the "Retail" category and then visited the "F&B" category right after - The count should be based on a two-point relationship that showcases the customers who convert from "Retail" to "F&B" as a direct link/flow the customers who convert Retail to F&B who visit "Retail" stores are ##% likely to visit "F&B" stores
INPUT
CustomerId   Zone Category   session start                               session_end                                         Date
1                        Retail               2020-01-06 02:21:41.000          2020-01-06 02:22:46.000                     2020-01-06
2                        F&B                 2020-01-06 16:05:50.000          2020-01-06 16:12:34.000                     2020-01-06

1                        F&B                 2020-01-06 03:22:46.000          2020-01-06 10:22:56.000                     2020-01-06

2                        L&E                 2020-01-10 18:55:23.000           2020-01-10 20:00:00:00                       2020-01-10 
need DAX Expression that comes the out like below for card visualization

customer 1 start his/her journey with Retail and convert to F&B then the count should be 1 (Retail -F&B)
customer 2 convert F&B to L&E then the count should be 1(F&B-L&E)

[Anonymous]

HI @manideep547 ,

Maybe you can try to use the following measure formula to calculate the category count of a specific customer:

Measure =
VAR currDate =
    MAX ( Table[session start] )
RETURN
    CALCULATE (
        COUNTA ( Table[Zone Category] ),
        FILTER ( ALLSELECTED ( Table ), [session start] > currDate ),
        VALUES ( Table[CustomerID] )
    )

Regards,

Xiaoxin Sheng

[amitchandak · Accepted solution]

This will give what is next Zone Category In the same row. Create a new column

minx(filter(table,table[CustomerId] = earlier(table[CustomerId]) && table[session start] > earlier(table[session start])),table[Zone Category])

This can be used to calculate.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[manideep547]

For direct query mode, minx not allowed for the calculated column. Here I need to display the count