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

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.

Reply
Douttful
Helper I
Helper I

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

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. 

 

PinnerKBA TItleIncident NumberLast Modified DateINC SubmitterKBA Pinned by TK CreatorINC Submit DateKBA Count
3040887KBA00003155INC00000094313710/29/2023 9:523040887110/29/2023 0:091
3040887KBA00006728INC00000094314010/29/2023 10:203040887110/29/2023 0:101
3040887KBA00000969INC00000094315110/29/2023 11:253040887110/29/2023 0:111
3040887KBA00001225INC00000094319310/29/2023 13:043040887110/29/2023 0:131
3040887KBA00002905INC00000094319310/31/2023 14:483040887110/29/2023 0:131
3040887KBA00006728INC00000094330410/29/2023 10:393040887110/29/2023 0:101
3040887KBA00002760INC00000094333010/29/2023 12:303040887110/29/2023 0:121
3040887KBA00001330INC00000094333710/29/2023 12:433040887110/29/2023 0:121
3040887KBA00003689INC00000094638610/31/2023 9:453040887110/31/2023 0:091
3040887KBA00001225INC00000094639410/31/2023 9:553040887110/31/2023 0:101
3040887KBA00002905INC00000094639410/31/2023 14:483040887110/31/2023 0:101
3040887KBA00001511INC00000094761711/1/2023 4:333040887111/1/2023 0:041
3040887KBA00002629INC00000094762111/1/2023 5:083040887111/1/2023 0:051
3040887KBA00002555INC00000094762111/1/2023 5:083040887111/1/2023 0:051
3040887KBA00001448INC00000094762411/1/2023 5:423040887111/1/2023 0:051
3040887KBA00003389INC00000094762811/1/2023 6:163040887111/1/2023 0:061
3040887KBA00001123INC00000094764311/1/2023 7:513040887111/1/2023 0:071
3040887KBA00006215INC00000094764711/1/2023 7:563040887111/1/2023 0:071
3040887KBA00000969INC00000094768211/1/2023 9:423040887111/1/2023 0:091
3040887KBA00000969INC00000094768211/1/2023 9:423040887111/1/2023 0:090
3040887KBA00005305INC00000094770011/1/2023 10:143040887111/1/2023 0:101
3040887KBA00004268INC00000094809611/1/2023 10:453040887111/1/2023 0:101
3040887KBA00003389INC00000094850911/1/2023 11:143040887111/1/2023 0:111
3040887KBA00001364INC00000094854311/1/2023 12:133040887111/1/2023 0:121
3040887KBA00001364INC00000094854311/1/2023 12:133040887111/1/2023 0:120
3040887KBA00001381INC00000094903111/2/2023 4:233040887111/2/2023 0:041
3040887KBA00005305INC00000094903411/2/2023 4:583040887111/2/2023 0:051
3040887KBA00003997INC00000094972111/2/2023 9:253040887111/2/2023 0:091
3040887KBA00001225INC00000094972811/2/2023 9:363040887111/2/2023 0:091
3040887KBA00002889INC00000094972811/2/2023 9:383040887111/2/2023 0:091
3040887KBA00003145INC00000094974311/2/2023 10:063040887111/2/2023 0:101
3040887KBA00003145INC00000094990211/2/2023 11:133040887111/2/2023 0:111

 

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! 

2 REPLIES 2
Douttful
Helper I
Helper I

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
Community Support
Community Support

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:

vjunyantmsft_0-1699244456254.png

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

vjunyantmsft_1-1699244511433.png

You can then use this DAX to count:

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

And the final output is shown in the following figure:

vjunyantmsft_2-1699244560215.png

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors