The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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! 🙂
Solved! Go to 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.
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.