Complex dynamic role filter

[Jan_Trummel]

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

[Jan_Trummel · Accepted solution]

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

[Jan_Trummel · Accepted solution]

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 · Accepted solution]

@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
)

[Jan_Trummel]

Hi @DataInsights ,

great idea, thank you! SWITCH makes it really much more easy!

Thank you!

[DataInsights]

@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]

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!