cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

Count

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)

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Count

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Count

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Helper III
Helper III

Re: Count

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

Highlighted
Community Support
Community Support

Re: Count

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

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

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Kudoed Authors