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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.