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.
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.
Solved! Go to 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.
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.
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.
If I answered your question, please mark my post as solution, this will also help others.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |