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
Jan_Trummel
Helper III
Helper III

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"Table "Orders"

 

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

Table "Roles"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

2 ACCEPTED SOLUTIONS
Jan_Trummel
Helper III
Helper III

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

View solution in original post

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Jan_Trummel
Helper III
Helper III

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,

 

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
)

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @DataInsights ,

 

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

 

Thank you!

DataInsights
Super User
Super User

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

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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!

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.