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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
carebear88
Frequent Visitor

How to count ID only once

Hi!

 

I need some help getting my data to only count once. The data has one unique ID number, but this number can have several rows due to a collum called consequences. This is because the ID can have several potential consequences added to them. I don't want to delete data, but be able to sort out only the cases one time ( count one time) so that I can see how many there are. 

For example lets say there is 100 cases, but I get 150 cases displayed due to several rows of an case ID.

 

Se sample data here;

Caseno   quality    potential consequence       date

12365        no      illness                                  14.03.2020

18865       yes      fire                                       02.12.2020

18547       yes      fire                                       04.11.2020

18865      yes      economical                         02.12.2020

17896      no       injury                                   25.06.2020

 

As you can see in the sample one case can be listed two times ( or more).  I do have another table where I have the same cases listed but without the category quality or potential consequence ( meaning that these case numbers are only counted once), but I am not able to build a relationship with them that enables me to connect the desired data.  They are connected with a many-to one relationship. I cant use the other table as the categories I need are not in that query.

 

Thank you for your help.

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi! Try to create a measure: 

Count ID once = DISTINCTCOUNT('TableName'[Caseno])

View solution in original post

1 REPLY 1
Anonymous
Not applicable

Hi! Try to create a measure: 

Count ID once = DISTINCTCOUNT('TableName'[Caseno])

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors