Forum Discussion

CharlotteCity12's avatar
CharlotteCity12
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago
Solved

Count Only One for Highest Category

Count/Sum Only One for Highest Crime per Incident ID (IID)

 

 

Data:

 

IIDs that have multiple charges, like IID 123 has three crimes A,B,C…  I only want to count the highest crime which is A for ID 123..

 

So for the above table I want the following results:

 

 

 

ID 123 only counts 1 A crime

ID 12 only counts 1 B crime

ID 13 only counts 1 B crime

ID 14 only counts 1 C crime

ID 15 only counts 1 B crime

Crime Levels:

Crime A highest

Crime B Second 

Crime C Third

Need Help
  • tackytechtom's avatar
    tackytechtom
    4 years ago

    Hi CharlotteCity12 ,

     

    I have a solution here:

     

     

    I did it in two steps. First I created a calculated column where each Crime is ranked per group. Since in your case, there is a natural order (A highest, B Second, C Third) this step was fairly easy but in a real life example, you might need to tweak that a bit...

    TomsRankColumn = 
RANKX(
    FILTER(
            'Table17',
            'Table17'[IID]=EARLIER('Table17'[IID])
            ),
    'Table17'[Crime],
    ,
    ASC
)

     

    After that, it was pretty simple to create a measure where we count over Crimes where the calculated column equals 1:

    TomsRankMeasure = 
CALCULATE ( 
    COUNT ( Table17[Crime] ),
    Table17[TomsRankColumn] = 1
)

     

    Hope this helps 🙂

     

    /Tom

    https://www.tackytech.blog

    https://www.instagram.com/tackytechtom

     

     

     

     

     

     

     

     

2 Replies

  • tackytechtom's avatar
    tackytechtom
    Icon for Most Valuable Professional rankMost Valuable Professional
    4 years ago

    Hi CharlotteCity12 ,

     

    I have a solution here:

     

     

    I did it in two steps. First I created a calculated column where each Crime is ranked per group. Since in your case, there is a natural order (A highest, B Second, C Third) this step was fairly easy but in a real life example, you might need to tweak that a bit...

    TomsRankColumn = 
RANKX(
    FILTER(
            'Table17',
            'Table17'[IID]=EARLIER('Table17'[IID])
            ),
    'Table17'[Crime],
    ,
    ASC
)

     

    After that, it was pretty simple to create a measure where we count over Crimes where the calculated column equals 1:

    TomsRankMeasure = 
CALCULATE ( 
    COUNT ( Table17[Crime] ),
    Table17[TomsRankColumn] = 1
)

     

    Hope this helps 🙂

     

    /Tom

    https://www.tackytech.blog

    https://www.instagram.com/tackytechtom