Forum Discussion

o59393's avatar
o59393
Post Prodigy
6 years ago
Solved

Count non duplicate values

Hi all

 

I need to have a kpi for the number of times a "C" appears in column called FSSC 22000

 

The formula I use is very simple:

 

 

FSSC 22000 Compliance = CALCULATE(COUNT('Suppliers Compliance'[FSSC 22000]),'Suppliers Compliance'[FSSC 22000]="C")

 

 

The KPI is calculated, however the problem is that i have duplicated values and the above formula is counting twice the duplicated value and I would like it to count only once.

 

Without duplicates I have 112 categorized as "C", with duplicates 127.

 

How can I have a dax that calculates the KPI considering only non duplicates.

 

A duplicate appears in the id column, for example there are 2 under 25:

 

 

I attach the pbix

 

https://1drv.ms/u/s!ApgeWwGTKtFdhjG8zPomag51V-hH?e=KiqKaA

 

Thanks

Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    6 years ago

    o59393 

    Have you tried distinctcount

    FSSC 22000 Compliance = CALCULATE(DISTINCTCOUNT('Suppliers Compliance'[FSSC 22000]),'Suppliers Compliance'[FSSC 22000]="C")

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    o59393 

    Have you tried distinctcount

    FSSC 22000 Compliance = CALCULATE(DISTINCTCOUNT('Suppliers Compliance'[FSSC 22000]),'Suppliers Compliance'[FSSC 22000]="C")
    • o59393's avatar
      o59393
      Post Prodigy
      6 years ago

      Thanks!

       

      Just did what you mentioned+ changing the denominator with the distinctcount also.

       

      FSSC 22000 Compliance = CALCULATE(DISTINCTCOUNT('Suppliers Compliance'[Id]),'Suppliers Compliance'[FSSC 22000]="C") / DISTINCTCOUNT('Suppliers Compliance'[Id])

       

      Regards.