PowerBI assistance creating a Measure to count the number of Like Occurances per ID

[JeremyKeith]

I am looking for a particular case to identify users in a large data set that are missing a expected roles role.

In the below example I have 3 users in my data set but the actual source is much larger. I ultimately would like to have a Measure or calculated column that returns either 0 for none or a count of of the filtered columns.

Example data 

Last Name	First Name	User Id	Role
Doe	John	doej	Building Access
Doe	John	doej	Special: Active Directory
Doe	John	doej	Special: Oracle
Smith	Joe	smithj12	20000001
Smith	Joe	smithj12	Security Professional
Smith	Joe	smithj12	Security Administrator
Smith	Joe	smithj12	Building Access
Smith	Joe	smithj12	Customer Service
Jones	Jenny	jonesj2	20000150
Jones	Jenny	jonesj2	20000150-U
Jones	Jenny	jonesj2	Building Access
Jones	Jenny	jonesj2	General Accounting
Jones	Jenny	jonesj2	Accounting Processor

In most cases users will only have one of these special roles and it will always start with 20, but there are some split roles that have 2. But as you can see John Doe in this example doesnt have a Numeric role listed and what I hope to be able to identify easier these. 

My ideal data output would be like the following.

User Id	Employee Role Detected
doej	0
smithj12	1
jonesj2	2

With  a measure I would be able to filter for Zero and identify what users do not have one of these 20 Roles. I may be thinking of this as a harder way that I need to  but I am still new to the tool so appritiate any help. I have tried various things but I have not made meaning full progress could anyone point to a example of the best way to do this.

[sturlaws · Accepted solution]

Hi @JeremyKeith ,

assuming I understand your requirements correct, you can write this measure:

Measure =
VAR _tmp =
    CALCULATE (
        COUNTROWS ( 'Table' );
        FILTER ( 'Table'; LEFT ( 'Table'[Role]; 2 ) = "20" )
    )
RETURN
    IF ( NOT ( ISBLANK ( _tmp ) ); _tmp; 0 )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[mwegener · Accepted solution]

Hi @JeremyKeith ,

try this

Employee Role Detected =
IF (
    ISBLANK (
        CALCULATE (
            COUNTROWS ( 'Table' ),
            FILTER ( 'Table', LEFT ( 'Table'[Role], 2 ) = "20" )
        )
    ),
    "No 20 role",
    BLANK ()
)

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[JeremyKeith]

@mwegener 

This looks like it will do what I need.

Thanks for the help

[mwegener]

@JeremyKeith 

If I answered your question, please mark my post as solution, this will also help others.

[sturlaws · Accepted solution]

Hi @JeremyKeith ,

assuming I understand your requirements correct, you can write this measure:

Measure =
VAR _tmp =
    CALCULATE (
        COUNTROWS ( 'Table' );
        FILTER ( 'Table'; LEFT ( 'Table'[Role]; 2 ) = "20" )
    )
RETURN
    IF ( NOT ( ISBLANK ( _tmp ) ); _tmp; 0 )

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[JeremyKeith]

@sturlaws Thanks for the responce it is appritiated.