Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Peter_K
New Member

Countrows if ID is unique

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:

startedinitialUserMessagemessageCountchatIdstartUrllogEvents

 

Here i have a example from the content, so i better can explain my wish:

Peter_K_0-1720616570276.png

 

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:

Hey25
Printer is offline8
My word tells me its invalid code1

 

What i have tried so far, either leave me with error, 1, 500, or even:

Hey574725
Printer is offline367824

 

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:

Peter_K_1-1720617355046.png

 

 Can you please help me with this problem?

 

Thank you in advance.

2 ACCEPTED SOLUTIONS
Irwan
Memorable Member
Memorable Member

Hello @Peter_K 

 

please check if this accomodate your need.

 

I made a random data based on your problem.

Irwan_2-1720653532171.png

 

Create a measure with following DAX:

Count =
var _Message = SELECTEDVALUE('Table'[Message])
Return
CALCULATE(DISTINCTCOUNT('Table'[ChatID]),FILTER('Table','Table'[Message]=_Message))
 
If this result doesnt match to your problem, please give a set of dummy data that can represent your whole data.
 
Hope this will help you.
Thank you.

View solution in original post

Irwan
Memorable Member
Memorable Member

Hello @Peter_K ,

 

that happens because of the filter in calculate DAX. The total seems not be able to be calculated properly.

Irwan_0-1720687545686.png

 

In my sample data, the total didnt even show up.

Irwan_1-1720687607703.png

 

To solve this, simply just remove the filter and use DISTINCTCOUNT directly

Irwan_2-1720687706304.png

 

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.

View solution in original post

7 REPLIES 7
Irwan
Memorable Member
Memorable Member

Hello @Peter_K 

 

please check if this accomodate your need.

 

I made a random data based on your problem.

Irwan_2-1720653532171.png

 

Create a measure with following DAX:

Count =
var _Message = SELECTEDVALUE('Table'[Message])
Return
CALCULATE(DISTINCTCOUNT('Table'[ChatID]),FILTER('Table','Table'[Message]=_Message))
 
If this result doesnt match to your problem, please give a set of dummy data that can represent your whole data.
 
Hope this will help you.
Thank you.

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?

Peter_K_0-1720675358587.png

 

Irwan
Memorable Member
Memorable Member

Hello @Peter_K ,

 

that happens because of the filter in calculate DAX. The total seems not be able to be calculated properly.

Irwan_0-1720687545686.png

 

In my sample data, the total didnt even show up.

Irwan_1-1720687607703.png

 

To solve this, simply just remove the filter and use DISTINCTCOUNT directly

Irwan_2-1720687706304.png

 

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.

Peter_K_0-1721020856620.png

 

Irwan
Memorable Member
Memorable Member

hello @Peter_K 

 

glad to be a help.


Thank you.

Irwan
Memorable Member
Memorable Member

hello @Peter_K 

 

glad to be a help.

 

Thank you.

Peter_K
New Member

I can't clean the JSON file for GDPR content.
The file has confidental data.
Does that mean you cant help me?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.