Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
slyfox
Helper II
Helper II

Indication of SoD conflicts

Hello,

 

The topic of Segregation of Duties (SoD) monitoring comes up frequently and now I am wondering how to make an analytical report that can show conflicts in SoD
I have two tables, one shows a list of users and a list of security groups that are assigned and the second table shows which security groups are in conflict with each other.

Does anyone have experience with such reports? and how to find users who are members of conflicting security groups.

2 ACCEPTED SOLUTIONS
johnt75
Super User
Super User

I'm making some assumptions about your model. Firstly, the list of users and security groups has multiple rows for each user, one row per security group they are a member of. Secondly, the conflicting security groups table has a primary group column and a conflicting group column, such that if groups A and B were conflicting there would be 2 rows in the table - one with A as primary and B as conflict and a second row with B as primary and A as conflict.

Given that, you could create a user dimension table like

Users = ALLNOBLANKROW('User security group'[User])

and then create a one-to-many relationship from the new dimension table to the user groups table. Add a new column to the dimension table like

Is in conflicting groups =
VAR UserGroups =
    VALUES ( 'User security group'[group] )
VAR ConflictingGroups =
    CALCULATETABLE (
        VALUES ( 'Conflicting groups'[conflicting group] ),
        TREATAS ( UserGroups, 'Conflicting groups'[Primary group] )
    )
VAR Result =
    NOT ISEMPTY ( INTERSECT ( UserGroups, ConflictingGroups ) )
RETURN
    Result

 

View solution in original post

would a simple COUNTROWS on the user table work ?

View solution in original post

5 REPLIES 5
johnt75
Super User
Super User

I'm making some assumptions about your model. Firstly, the list of users and security groups has multiple rows for each user, one row per security group they are a member of. Secondly, the conflicting security groups table has a primary group column and a conflicting group column, such that if groups A and B were conflicting there would be 2 rows in the table - one with A as primary and B as conflict and a second row with B as primary and A as conflict.

Given that, you could create a user dimension table like

Users = ALLNOBLANKROW('User security group'[User])

and then create a one-to-many relationship from the new dimension table to the user groups table. Add a new column to the dimension table like

Is in conflicting groups =
VAR UserGroups =
    VALUES ( 'User security group'[group] )
VAR ConflictingGroups =
    CALCULATETABLE (
        VALUES ( 'Conflicting groups'[conflicting group] ),
        TREATAS ( UserGroups, 'Conflicting groups'[Primary group] )
    )
VAR Result =
    NOT ISEMPTY ( INTERSECT ( UserGroups, ConflictingGroups ) )
RETURN
    Result

 

@johnt75  In the Matrix table, in the rows - the main group, in the column - the conflicting group, how to show in the Values the number of users?

would a simple COUNTROWS on the user table work ?

-

Hi,

Share some data, describe the question and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors