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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare 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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.