Creating Buckets with Amount of Conversations for each of the user

[Anonymous]

Hi All,

I have a log file from the database of the chatbot. I would like to get to know how to create the columns where I would specify in boolean form the number of conversations per user. 

My input data format is the following one: 

I created the measure specifying the distinc amount of conversations per user 

NoOFConversations = DISTINCTCOUNT('dcb'[ConversationId])

 getting the following output:

I would like to be able to create the graph which on the X axis will have lets say months and the % of Users who had 0 Conversations, 1 Conversation, 2-5 Conversations and 6+ COnversations. I need to find a formula which will count the number of users based on the criteria mentioned above. I think I should create columns specifying these four cases but I do not know how to do it. Please help! 🙂

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create column like DAX below.

Conversation Group =
var NoOFConversations= CALCULATE(DISTINCTCOUNT('dcb'[ConversationId]),FILTER('dcb','dcb'[UserId]=EARLIER('dcb'[UserId])))
return
SWITCH(TRUE(), NoOFConversations =0,"0 Conversation", NoOFConversations =1,"1 Conversation", NoOFConversations >=2&&NoOFConversations <=5,"2-5 Conversations", NoOFConversations >=6,"6+ Conversations")

Then you can choose Clustered column chart to display result, put this new column onto Column values box, set it show value as " percent of grand total ".

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.