Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to remove rows of data from an imported excel file to find the proper count of an enquiry type.
I have three columns of Date, ReferenceNo and Enquiry Type.
When an Assigned enquiry type is created, automatically a new row of data is created with an Edit enquiry type. So you get two rows of data for one action.
I would like to count only the enquiry type Edit but not the Edit that is associated with the enquiry type Assigned which are related by the duplicated ReferenceNo.
Here is a sample table noting the duplicate Reference number. The correct count of Edit should be 6 and not 10.
Date | ReferenceNo | Enquiry Type |
10/05/2019 | 000-012 | Assigned |
10/05/2019 | 000-012 | Edit |
10/05/2019 | 000-123 | Assigned |
10/05/2019 | 000-123 | Edit |
10/05/2019 | 000-456 | Edit |
10/05/2019 | 000-789 | Edit |
10/05/2019 | 000-963 | Edit |
10/05/2019 | 000-852 | Edit |
10/05/2019 | 000-741 | Edit |
10/05/2019 | 000-753 | Assigned |
10/05/2019 | 000-753 | Edit |
10/05/2019 | 000-951 | Assigned |
10/05/2019 | 000-951 | Edit |
10/05/2019 | 000-486 | Edit |
Thanks in advance for any assistance given.
Hi,
This measure works
=COUNTROWS(FILTER(SUMMARIZE(Data,Data[ReferenceNo],Data[Enquiry Type],"ABCD",CALCULATE(DISTINCTCOUNT(Data[Enquiry Type]),ALL(Data[Enquiry Type]))),Data[Enquiry Type]="Edit"&&[ABCD]=1))
Hope this helps.
Thanks @Ashish_Mathur
Unfortunately I can get that unique number already but don't think I explained myself well enough.
Each reference number is worked on by a person. It creates two entries automatically when the person assigns the reference number to another person. So we have a fourth column with the person's name which is repeated based on what reference number they work on. Usually one person assigns reference numbers to other people and that makes their unique edit count high. I am essentially wanting to count reference numbers where the person only edits and not assigned and therefore edited. So counting unique reference numbers but not when they are repeated with Enquiry Type = Assigned.
I'd prefer not to go through the Excel file to manually delete the two lines for Assigned and Edit.
Hope that makes sense and appreciate any assistance given.
Why not just remove the duplicates of ReferenceNo?
it will keep the ones with Type Assignes since they are mentioned before the Edit ones.
The edits will then only be true edits.
The issue I am finding is that with more rows of data I get a number returned but that number is different to manually removing the Edit and Assigned Enquiry Type that share the same Reference No.
Thanks for the input so far, appreciated.
Hi,
Unfortunately, i do not think i understand your requirement. I got the exact result you wanted (as mentioned in your first post).
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |