Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
Hi! Try to create a measure:
Count ID once = DISTINCTCOUNT('TableName'[Caseno])
Hi! Try to create a measure:
Count ID once = DISTINCTCOUNT('TableName'[Caseno])
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
17 | |
15 |