Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
Hello. I have browsed your solutions here, but coudn't find anything i could use in my situation.
I am all new to PowerBI.
I have a JSON datasource, with 500 chats in it. It has following columns:
started | initialUserMessage | messageCount | chatId | startUrl | logEvents |
Here i have a example from the content, so i better can explain my wish:
Every chat has a unique chatId. So initialUserMessage appear on multiple rows with that unique chatId. 1 row for each text sent from and to the user.
What i like to achieve is a table where it counts initialUserMessage only one time, for each unique chatId. ie:
Hey | 25 |
Printer is offline | 8 |
My word tells me its invalid code | 1 |
What i have tried so far, either leave me with error, 1, 500, or even:
Hey | 574725 |
Printer is offline | 367824 |
I dont seem to get it right.
The formula i tried and messed around with, is like this now, and gives value 500 (the total chats)
test1 = CALCULATE(DISTINCTCOUNT(chat_logs[chatId]),FILTER(chat_logs, chat_logs[initialUserMessage]=chat_logs[initialUserMessage]))
Here is the structure of JSON import, where test1 is the one i try this in:
Can you please help me with this problem?
Thank you in advance.
Solved! Go to Solution.
Hello @Peter_K
please check if this accomodate your need.
I made a random data based on your problem.
Create a measure with following DAX:
Hello @Peter_K ,
that happens because of the filter in calculate DAX. The total seems not be able to be calculated properly.
In my sample data, the total didnt even show up.
To solve this, simply just remove the filter and use DISTINCTCOUNT directly
In fact, if your data has uniqueID for every message, I think direct distinctcount should do the job (no need put any filter by calculate DAX).
But please give it a try first and let see if you get the correct value or not by using direct distinctcount.
Hope this will help you.
Thank you.
Hello @Peter_K
please check if this accomodate your need.
I made a random data based on your problem.
Create a measure with following DAX:
Thank you so much @Irwan That totally solved my problem.
I am so happy now, i really tried so many things, nothing worked the way i wanted.
You solved it, thank you. Never thought it was under measure.
You know why it show 14 as total?
Hello @Peter_K ,
that happens because of the filter in calculate DAX. The total seems not be able to be calculated properly.
In my sample data, the total didnt even show up.
To solve this, simply just remove the filter and use DISTINCTCOUNT directly
In fact, if your data has uniqueID for every message, I think direct distinctcount should do the job (no need put any filter by calculate DAX).
But please give it a try first and let see if you get the correct value or not by using direct distinctcount.
Hope this will help you.
Thank you.
Thank you very much.
This solved it.
I know there is exact 500 chats, and thats also the result.
You solved my problem, thanks.
I can't clean the JSON file for GDPR content.
The file has confidental data.
Does that mean you cant help me?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
10 | |
10 | |
9 | |
9 |
User | Count |
---|---|
20 | |
13 | |
12 | |
11 | |
8 |