Forum Discussion

Jan_Trummel's avatar
Jan_Trummel
Helper IV
2 years ago
Solved

Complex dynamic role filter

Hello to the forum,

 

I have a question about a role filter in Power BI Desktop. Power BI is intended to filter the Orders table so that each person only sees the rows intended for them.

 

Here is the table Orders:

Table "Orders"

 

The Roles table determines who is allowed to see which data. Here is the Roles table:

Table "Roles"

 

Let's go through the different cases:
Charlie
The user Charlie is allowed to see all rows in the Orders table because there is no entry in the columns Location and Client.
Alan
The user Alan can see all orders from New York. He can also see the orders from clients A and B from Washington.
Judith
The user Judith can see the orders from New York from clients A and B. They should also have access to the orders from clients A and C from Los Angeles.
Berta
The user Berta is allowed to see all orders from clients A and B, regardless of the location.

 

Since there can be new entries in the Roles table at any time or because existing entries can change, the DAX code must be dynamic.

 

Do you have any idea how I can solve this problem?

 

Many thanks and greetings

Need Help
  • Jan_Trummel's avatar
    Jan_Trummel
    2 years ago

    Hi DataInsights ,

     

    I found that solution for my role in table Orders:

     

    IF(
        SUMX(
            ADDCOLUMNS(
                FILTER(Roles, Roles[Account] = USERPRINCIPALNAME()),
                "Prüfung",
                IF(
                    ISBLANK(Roles[Location]) && ISBLANK(Roles[Client]),
                    1,
                    IF(
                        Roles[Location] = Orders[Location]
                        && ISBLANK(Roles[Client]),
                        1,
                        IF(
                            ISBLANK(Roles[Location]) && 
                            Roles[Client] = Orders[Client],
                            1,
                            IF(
                                Roles[Location] = Orders[Location]
                                && Roles[Client] = Orders[Client],
                                1,
                                0
                            )
                        )
                    )
                )
            ),
            [Prüfung]
        ) > 0,
        TRUE(),
        FALSE()
    )

    That work's very fine.

     

    Thank you and have a nice day!

     

    Greetings

  • DataInsights's avatar
    DataInsights
    2 years ago

    Jan_Trummel,

     

    Great solution! A simpler way to write it uses SWITCH instead of nested IFs, which scales well if you add more columns:

     

    IF (
    SUMX (
        ADDCOLUMNS (
            FILTER ( Roles, Roles[Account] = USERPRINCIPALNAME () ),
            "Prüfung",
                SWITCH (
                    TRUE,
                    ISBLANK ( Roles[Location] ) && ISBLANK ( Roles[Client] ), 1,
                    Roles[Location] = Orders[Location]
                        && ISBLANK ( Roles[Client] ), 1,
                    Roles[Client] = Orders[Client]
                        && ISBLANK ( Roles[Location] ), 1,
                    Roles[Location] = Orders[Location]
                        && Roles[Client] = Orders[Client], 1,
                    0
                )
        ),
        [Prüfung]
    ) > 0,
    TRUE,
    FALSE
)

     

5 Replies

  • Jan_Trummel's avatar
    Jan_Trummel
    Helper IV
    2 years ago

    Hi DataInsights ,

     

    I found that solution for my role in table Orders:

     

    IF(
        SUMX(
            ADDCOLUMNS(
                FILTER(Roles, Roles[Account] = USERPRINCIPALNAME()),
                "Prüfung",
                IF(
                    ISBLANK(Roles[Location]) && ISBLANK(Roles[Client]),
                    1,
                    IF(
                        Roles[Location] = Orders[Location]
                        && ISBLANK(Roles[Client]),
                        1,
                        IF(
                            ISBLANK(Roles[Location]) && 
                            Roles[Client] = Orders[Client],
                            1,
                            IF(
                                Roles[Location] = Orders[Location]
                                && Roles[Client] = Orders[Client],
                                1,
                                0
                            )
                        )
                    )
                )
            ),
            [Prüfung]
        ) > 0,
        TRUE(),
        FALSE()
    )

    That work's very fine.

     

    Thank you and have a nice day!

     

    Greetings

    • DataInsights's avatar
      DataInsights
      Super User
      2 years ago

      Jan_Trummel,

       

      Great solution! A simpler way to write it uses SWITCH instead of nested IFs, which scales well if you add more columns:

       

      IF (
    SUMX (
        ADDCOLUMNS (
            FILTER ( Roles, Roles[Account] = USERPRINCIPALNAME () ),
            "Prüfung",
                SWITCH (
                    TRUE,
                    ISBLANK ( Roles[Location] ) && ISBLANK ( Roles[Client] ), 1,
                    Roles[Location] = Orders[Location]
                        && ISBLANK ( Roles[Client] ), 1,
                    Roles[Client] = Orders[Client]
                        && ISBLANK ( Roles[Location] ), 1,
                    Roles[Location] = Orders[Location]
                        && Roles[Client] = Orders[Client], 1,
                    0
                )
        ),
        [Prüfung]
    ) > 0,
    TRUE,
    FALSE
)

       

  • DataInsights's avatar
    DataInsights
    Super User
    2 years ago

    Jan_Trummel,

     

    Try this expression in an Orders table role:

     

    SWITCH (
    TRUE,
    // Location is blank AND Client is blank
    ISEMPTY (
        CALCULATETABLE (
            VALUES ( Roles[Location] ),
            ISBLANK ( Roles[Location] ),
            Roles[Account] = USERPRINCIPALNAME ()
        )
    )
        && ISEMPTY (
            CALCULATETABLE (
                VALUES ( Roles[Client] ),
                ISBLANK ( Roles[Client] ),
                Roles[Account] = USERPRINCIPALNAME ()
            )
        ), TRUE,
    // Location is not blank AND Client is blank
    ISEMPTY (
        CALCULATETABLE (
            VALUES ( Roles[Location] ),
            NOT ISBLANK ( Roles[Location] ),
            Roles[Account] = USERPRINCIPALNAME ()
        )
    )
        && ISEMPTY (
            CALCULATETABLE (
                VALUES ( Roles[Client] ),
                ISBLANK ( Roles[Client] ),
                Roles[Account] = USERPRINCIPALNAME ()
            )
        ),
        Orders[Location]
            IN CALCULATETABLE (
                VALUES ( Roles[Location] ),
                Roles[Account] = USERPRINCIPALNAME ()
            ),
    // Location is blank AND Client is not blank
    ISEMPTY (
        CALCULATETABLE (
            VALUES ( Roles[Location] ),
            ISBLANK ( Roles[Location] ),
            Roles[Account] = USERPRINCIPALNAME ()
        )
    )
        && ISEMPTY (
            CALCULATETABLE (
                VALUES ( Roles[Client] ),
                NOT ISBLANK ( Roles[Client] ),
                Roles[Account] = USERPRINCIPALNAME ()
            )
        ),
        Orders[Client]
            IN CALCULATETABLE (
                VALUES ( Roles[Client] ),
                Roles[Account] = USERPRINCIPALNAME ()
            ),
    // Location is not blank AND Client is not blank
    Orders[Location]
        IN CALCULATETABLE (
            VALUES ( Roles[Location] ),
            Roles[Account] = USERPRINCIPALNAME ()
        )
            && Orders[Client]
                IN CALCULATETABLE (
                    VALUES ( Roles[Client] ),
                    Roles[Account] = USERPRINCIPALNAME ()
                )
)

     

    • Jan_Trummel's avatar
      Jan_Trummel
      Helper IV
      2 years ago

      Hello DataInsights ,

       

      thanks for your answer. Your approach still doesn't work completely as I hoped. But I think I can keep working with it.

       

      As soon as I have a solution that serves my purposes, I will share it with you. Thank you again!