Forum Discussion

Matej_SVK's avatar
Matej_SVK
Frequent Visitor
5 years ago
Solved

DISTINCTCOUNT without unique values in a column

Dear all, I´m trying to solve following problem:

 

I have a table of Process IDs, e.g.:

 

Process ID

CM001

CM001

CM002

CM002

CM003

CM004

CM004

 

By using the DISTINCTCOUNT function in a measure (can´t use calculated columns, because I´m using slicers and I need dynamic number), I get the result of 4. Is it somehow possible to exclude from this final number Process ID (in this case CM003) that is appearing in my column only once?

 

The aim of this calculation is to get the % of standardization - formula:

"DISTINCTCOUNT of Process IDs without unique Process IDs (in this case 3)" - divided by - "Total number of Process IDs (in this case 7)"

 

Maybe I´m looking at the solution from a wrong perspective.

 

Thank you for any help or suggestion 🙂

 

BR,

Matej

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

    Hi, Matej_SVK 

    I am not sure if I understood your question correctly, but please check the below picture and the sample pbix file's link down below.

     

     

    distinct count excluding only once appearing =
    VAR newtable =
    SUMMARIZE (
    FILTER (
    SUMMARIZE ( 'Table', 'Table'[Process ID], "@result", COUNTROWS ( 'Table' ) ),
    [@result] > 1
    ),
    'Table'[Process ID]
    )
    RETURN
    COUNTROWS ( newtable )

     

    https://www.dropbox.com/s/hkvxbr1u41soj8i/matej.pbix?dl=0 

     

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

     

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

    Hi, Matej_SVK 

    I am not sure if I understood your question correctly, but please check the below picture and the sample pbix file's link down below.

     

     

    distinct count excluding only once appearing =
    VAR newtable =
    SUMMARIZE (
    FILTER (
    SUMMARIZE ( 'Table', 'Table'[Process ID], "@result", COUNTROWS ( 'Table' ) ),
    [@result] > 1
    ),
    'Table'[Process ID]
    )
    RETURN
    COUNTROWS ( newtable )

     

    https://www.dropbox.com/s/hkvxbr1u41soj8i/matej.pbix?dl=0 

     

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM