Help Writing DAX Expression Need to count number of values in a table, but ignore duplicate rows

[Douttful]

Hello all, 

I utilize power BI for reporting in my role, and I need help writing a DAX expression.  I work in a call center. And we are able to export data from our ticketing system. This ticketing system generates a unique incident number for each ticket created. We also have a knowledge base that we use in our department with various knowledge articles (KBA Title), if a team member follows a spcifiec knowledge article (KBA Title) when working/ or troubleshooting an issue, they are supposed to pin the knowledge article to the incident number in our ticketing system. I need to calculate the percentage of how many knowledge articles are pinned compaired to incident numbers created.

Step 1 of this is to figure out how many knowledge articles are pinned. When reviewing the data I've found that sometime the rows are duplicating/ showing multiple rows with the same incident and KBA Title. If multiple KBA Titles are used on the same Incident Number, that's fine. but I need to ignore duplicate rows with the same Incident Number and same KBA Title when counting.  

Below is an example data source. I added the KBA Count column manually to illustrate what I am trying to acomplish. In red text is an example of the duplicated rows I need to ignore in counting. I count the first occurance, but do not count the second. 

Pinner	KBA TItle	Incident Number	Last Modified Date	INC Submitter	KBA Pinned by TK Creator	INC Submit Date	KBA Count
3040887	KBA00003155	INC000000943137	10/29/2023 9:52	3040887	1	10/29/2023 0:09	1
3040887	KBA00006728	INC000000943140	10/29/2023 10:20	3040887	1	10/29/2023 0:10	1
3040887	KBA00000969	INC000000943151	10/29/2023 11:25	3040887	1	10/29/2023 0:11	1
3040887	KBA00001225	INC000000943193	10/29/2023 13:04	3040887	1	10/29/2023 0:13	1
3040887	KBA00002905	INC000000943193	10/31/2023 14:48	3040887	1	10/29/2023 0:13	1
3040887	KBA00006728	INC000000943304	10/29/2023 10:39	3040887	1	10/29/2023 0:10	1
3040887	KBA00002760	INC000000943330	10/29/2023 12:30	3040887	1	10/29/2023 0:12	1
3040887	KBA00001330	INC000000943337	10/29/2023 12:43	3040887	1	10/29/2023 0:12	1
3040887	KBA00003689	INC000000946386	10/31/2023 9:45	3040887	1	10/31/2023 0:09	1
3040887	KBA00001225	INC000000946394	10/31/2023 9:55	3040887	1	10/31/2023 0:10	1
3040887	KBA00002905	INC000000946394	10/31/2023 14:48	3040887	1	10/31/2023 0:10	1
3040887	KBA00001511	INC000000947617	11/1/2023 4:33	3040887	1	11/1/2023 0:04	1
3040887	KBA00002629	INC000000947621	11/1/2023 5:08	3040887	1	11/1/2023 0:05	1
3040887	KBA00002555	INC000000947621	11/1/2023 5:08	3040887	1	11/1/2023 0:05	1
3040887	KBA00001448	INC000000947624	11/1/2023 5:42	3040887	1	11/1/2023 0:05	1
3040887	KBA00003389	INC000000947628	11/1/2023 6:16	3040887	1	11/1/2023 0:06	1
3040887	KBA00001123	INC000000947643	11/1/2023 7:51	3040887	1	11/1/2023 0:07	1
3040887	KBA00006215	INC000000947647	11/1/2023 7:56	3040887	1	11/1/2023 0:07	1
3040887	KBA00000969	INC000000947682	11/1/2023 9:42	3040887	1	11/1/2023 0:09	1
3040887	KBA00000969	INC000000947682	11/1/2023 9:42	3040887	1	11/1/2023 0:09	0
3040887	KBA00005305	INC000000947700	11/1/2023 10:14	3040887	1	11/1/2023 0:10	1
3040887	KBA00004268	INC000000948096	11/1/2023 10:45	3040887	1	11/1/2023 0:10	1
3040887	KBA00003389	INC000000948509	11/1/2023 11:14	3040887	1	11/1/2023 0:11	1
3040887	KBA00001364	INC000000948543	11/1/2023 12:13	3040887	1	11/1/2023 0:12	1
3040887	KBA00001364	INC000000948543	11/1/2023 12:13	3040887	1	11/1/2023 0:12	0
3040887	KBA00001381	INC000000949031	11/2/2023 4:23	3040887	1	11/2/2023 0:04	1
3040887	KBA00005305	INC000000949034	11/2/2023 4:58	3040887	1	11/2/2023 0:05	1
3040887	KBA00003997	INC000000949721	11/2/2023 9:25	3040887	1	11/2/2023 0:09	1
3040887	KBA00001225	INC000000949728	11/2/2023 9:36	3040887	1	11/2/2023 0:09	1
3040887	KBA00002889	INC000000949728	11/2/2023 9:38	3040887	1	11/2/2023 0:09	1
3040887	KBA00003145	INC000000949743	11/2/2023 10:06	3040887	1	11/2/2023 0:10	1
3040887	KBA00003145	INC000000949902	11/2/2023 11:13	3040887	1	11/2/2023 0:11	1

The total for this data should be 30 KBAs. However, I cannot get the DAX to display that information. I am using Distinct count and Count to try and calculate the data. I don't know of a better way to model the data  so I can automatically generate the KBA count column. 

Got this from a separate PBI Forum post that sounded close. It is returning a count of all Incident Numbers 

KBA Use Count 2 = CALCULATE(DISTINCTCOUNT('KBA Linked to Incidents CSV'[Incident Number]), 'KBA Linked to Incidents CSV'[KBA Pinned by TK Creator]=1, FILTER(SUMMARIZE(VALUES('KBA Linked to Incidents CSV'[KBA ID]),"ABCD", COUNTROWS('KBA Linked to Incidents CSV')), [ABCD]>1))

This is me throwing things together after trying to talk it out. 

KBA Use Count 5 = CALCULATE(DISTINCTCOUNT('KBAs Linked to Incidents CSV'[KBA TItle]), DISTINCT('KBAs Linked to Incidents CSV'[Incident Number]), 'KBAs Linked to Incidents CSV'[Pinned by Submitter]=1) 

I am at a loss so reaching out for assistance. 

Thanks for everyone's time! 

[Douttful]

Thank you for this great explanation. Unfortunately it is not what I need. There will be duplicate KBA IDs because the same KBA will be used for different Incidents. I need to count the first occurance of an incident number and KBA ID together and ignore any other rows with the same incident number - KBA combo.  

[v-junyant-msft]

Hi @Douttful ,

If you only need to count the number of unique KBA Titles,
The following DAX will help you tag each KBA Title, with the first occurrence marked as 1 and the repeated occurrence marked as 0:

Column = IF(
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[KBA TItle] = EARLIER('Table'[KBA TItle]) &&
            'Table'[Index] < EARLIER('Table'[Index])
        )
    ) > 0,
    0,1
)

And the final output is shown in the following figure:

'Index' is an index column created in Power Query that starts from 1 to mark the order of precedence:

You can then use this DAX to count:

KBA count = 
DISTINCTCOUNT('Table'[KBA TItle])

And the final output is shown in the following figure:

Best Regards,

Dino Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.